awesome

@AccessAnalytic

Жыл бұрын

Cheers Bernat

The tips are great and so is the guitar music at the end! 🎸

@AccessAnalytic

5 күн бұрын

Cheers!

Excellent tip to deal with annoying error in pivoting a column. Thank you Wyn

@AccessAnalytic

Жыл бұрын

You’re welcome Kebin

Believe me today I needed it and fortunately I found my solution in this video.. thank you very much sir

@AccessAnalytic

Жыл бұрын

You’re very welcome

very helpful, you've saved my day - thanks a lot, and the bonus tip was very helpful as well👍👌😊

@AccessAnalytic

4 ай бұрын

Glad to help

Great tip, Wyn! You always pleasantly surprise us with these weekly tips. ❤

@AccessAnalytic

Жыл бұрын

Thanks Krishan 🤗

Brilliant, exact what I want to learn! Thank you

@AccessAnalytic

7 ай бұрын

Thanks for letting me know it was useful 😀

Wowww.. just what I needed ..thanks a lot 🙂

@AccessAnalytic

9 күн бұрын

You’re welcome. I appreciate you taking the time to let me know you found it useful

Sir, usted no sabe como lo amo en este momento!

@AccessAnalytic

5 ай бұрын

Glad it was useful

always a pleasure learning from you on Saturday.

@AccessAnalytic

Жыл бұрын

😀glad to teach ( and learn ) Abdul

Thank you for clear explanation.

@AccessAnalytic

9 ай бұрын

You're welcome

Nice video. Many thanks

@AccessAnalytic

9 ай бұрын

You’re welcome

Awesome! Very powerful.

@AccessAnalytic

4 ай бұрын

Absolutely

Nice trick... you're a power query wizard¡¡¡ ;)

@AccessAnalytic

Жыл бұрын

Thank you 😄

Thanks for great lesson 👍. But i have one small remark. If the source table is not sorted by month then the order of the rows in the result table may be incorrect. So, i propose add as a first step of query (it doesn't have to be the first step, but for clarity, I like it to be at the beginning) MonthsOrder = Table.FromColumns( { List.Transform( List.Dates(#date(1, 1, 15), 12, #duration(30, 0, 0, 0)), each Date.ToText(_, "MMM", "en-GB") ), {1 .. 12} }, {"Months", "Idx"} ) Then the "Merged Queries" step and the following would look like this below #"Merged Queries" = Table.NestedJoin( ReadyForMerge, {"Month"}, MonthsOrder2, {"Month"}, "MonthsOrder", JoinKind.LeftOuter ), Expanded = Table.ExpandTableColumn( #"Merged Queries", "MonthsOrder", {"Idx"}, {"Idx"}), #"Sorted Rows" = Table.Sort( Expanded, {{"Idx", Order.Ascending}, {"Index", Order.Ascending}} ), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows", {"Index", "Idx"}) in #"Removed Columns" The advantage of this solution is that we can have the names of the months in any language (just change "en-GB" to, for example, "pl-PL")

@AccessAnalytic

Жыл бұрын

Nice suggestion Bill

grate solution. thank you

@AccessAnalytic

Жыл бұрын

Glad it helped

Very helpful video, thanks Wyn!

@AccessAnalytic

Жыл бұрын

You’re welcome Chris. Thanks for taking the time to leave a kind comment

Awesome, Thanks a lot❤

@AccessAnalytic

Ай бұрын

You're welcome

Excellent!

@AccessAnalytic

Жыл бұрын

Cheers

so simple and so useful !!!

@AccessAnalytic

9 ай бұрын

Glad to help

Nice tricks

@AccessAnalytic

Жыл бұрын

Thanks Bhavik

Very useful! I needed this for a two-column data set as an alternative to my complicated let/drop/reduce/lambda formula.

@AccessAnalytic

5 ай бұрын

I appreciate you taking the time to let me know you found it useful

@dougmphilly

2 ай бұрын

anything is better than lambda. you hand offv a spreadsheet to anyone with that function all you get is stares.

@TopBam

2 ай бұрын

@@dougmphilly I still love LAMBDA! Greatest thing to hit excel since power query came out for Excel 2010. It is a real game changer, just like power query.

@dougmphilly

2 ай бұрын

@@TopBam its only intuitive to the most powerful of power users. i tried explaining the function in a shared spreadsheet that went absolutely nowhere.

@TopBam

2 ай бұрын

@@dougmphilly They can learn if they are taught correctly. Lack of competence is problem in every profession. Just because people don't understand it, doesn't mean we should not use it.

Great one, thanks!

@AccessAnalytic

Жыл бұрын

Cheers Mariusz

Very educative!

@AccessAnalytic

Жыл бұрын

Thank you

Super smart and useful

@AccessAnalytic

Жыл бұрын

Cheers Alex

Thank you!

@AccessAnalytic

Жыл бұрын

You’re welcome

thank you sir

@AccessAnalytic

Жыл бұрын

You’re welcome

Thanks for an amazing solution. I think you can add helper column in source table to make it simple for sort order. =MONTH(DATEVALUE("1-"&[@Month]&"-1900"))

@AccessAnalytic

8 ай бұрын

Cheers

Awesome 👍👍👍

@AccessAnalytic

6 ай бұрын

Thanks

Loved it.

@AccessAnalytic

Жыл бұрын

Thanks Bhaskar

Like the sort trick, and thanks for mentioning about Upper before lower case sorting in previous, when I did this I had a jan in wrong order, and if you hadn't mentioned that upper sort first would have spent good while wondering why.

@AccessAnalytic

Жыл бұрын

You’re welcome 😀

Genius!!!!!

@AccessAnalytic

Жыл бұрын

Thank you 🤗

Bahebak❤

hi Wyn. I am wondering if we just add index to the intial table and then pivot without grouping. Would that also prevent error from popping up? Thanks

@AccessAnalytic

Жыл бұрын

Yep, sure does but you get a single row for every entry so it looks pretty messy

@kebincui

Жыл бұрын

@@AccessAnalytic Thanks Wyn for your quick clarification❤👍

I concatenate 01-jan-2023 to make a custom column of date. The. Sort by that custom date column

@AccessAnalytic

Жыл бұрын

Nice one 😀

why did you have to group the data before adding the Index column? does it mean it wouldn't have worked if you'd added the index column without grouping the data?

@AccessAnalytic

6 ай бұрын

It helps keep the records together. So entry 1 for each person appears on same row, then entry 2 etc, otherwise it splits each entry into a unique row

@pascaljoly5752

6 ай бұрын

@@AccessAnalytic makes sense. i have managed to get the same result by creating a function that adds an index column in each sub-table that you get after grouping the data and before expanding the data. at the end of the day, it's pretty much doing the same thing but since i am new to creating functions, it made more sense to me this way to try this way. i could send you what i have done but i don't know how.

@pascaljoly5752

6 ай бұрын

the function i wrote is basic: (MyTable) => let #"Added Index" = Table.AddIndexColumn(MyTable, "Index", 1, 1, Int64.Type), #"Changed Type3" = Table.TransformColumnTypes(#"Added Index",{{"Index", type number}}) in #"Changed Type3" then all i have to do is to invoke it once i've grouped the data. seems to do the job just fine as i get the same result. am i missing something?

@AccessAnalytic

6 ай бұрын

Sounds fine to me

DataFormat.Error: Invalid cell value '#NAME?'. How to solve this error

@AccessAnalytic

10 ай бұрын

That sounds like an error in the source excel file.