How to use Pivot in Power Query and avoid the errors (PLUS bonus at minute 6) 

awesome

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

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

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

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

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

always a pleasure learning from you on Saturday.

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

Very helpful video, thanks Wyn!

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

Thank you for clear explanation.

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")

Brilliant, exact what I want to learn! Thank you

so simple and so useful !!!

Awesome! Very powerful.

Nice video. Many thanks

Very educative!

grate solution. thank you

Great one, thanks!

Excellent!

Awesome, Thanks a lot❤

Super smart and useful

Loved it.

Awesome 👍👍👍

Thank you!

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

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.

Genius!!!!!

thank you sir

Nice tricks

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

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"))

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

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

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?

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