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

⏬ Click to see more:
If you ever need to Pivot your data it's quite possible you've hit an error.
Here I explain why, and how to avoid it
00:00 The Intro
00:17 The Setup
01:20 Getting the Error
02:15 Why the error?
02:56 The solution
03:11 Group By
03:56 Add an Index column within the group
05:32 The Pivot now works!
05:55 BONUS PRO TIP: Merging the Query with itself 🤯
File to download:
aasolutions.sharepoint.com/:f...
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

[@AgulloBernat]

awesome

[@AccessAnalytic]

Cheers Bernat

[@Looii5]

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

[@AccessAnalytic]

Cheers!

[@kebincui]

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

[@AccessAnalytic]

You’re welcome Kebin

[@zahoorsarbandi2982]

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

[@krishanphophalia9303]

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

[@AccessAnalytic]

Thanks Krishan 🤗

[@sebastianschick1989]

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

[@AccessAnalytic]

Glad to help

[@rahulkalingeri1206]

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

[@AccessAnalytic]

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

[@BillSzysz1]

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

[@abdulsaidmkamara7305]

always a pleasure learning from you on Saturday.

[@AccessAnalytic]

😀glad to teach ( and learn ) Abdul

[@MoRo9083]

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

[@AccessAnalytic]

Glad it was useful

[@ksowjanya4488]

Thank you for clear explanation.

[@AccessAnalytic]

You're welcome

[@TopBam]

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

[@AccessAnalytic]

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

[@dougmphilly]

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

[@TopBam]

@@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]

@@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]

@@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.

[@chrism9037]

Very helpful video, thanks Wyn!

[@AccessAnalytic]

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

[@behzadghah]

grate solution. thank you

[@AccessAnalytic]

Glad it helped

[@shubhabratadey]

Nice video. Many thanks

[@AccessAnalytic]

You’re welcome

[@109zxg]

Brilliant, exact what I want to learn! Thank you

[@AccessAnalytic]

Thanks for letting me know it was useful 😀

[@user-en3pc7ko8e]

Awesome! Very powerful.

[@AccessAnalytic]

Absolutely

[@williamarthur4801]

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 😀

[@gsbertho]

so simple and so useful !!!

[@AccessAnalytic]

Glad to help

[@HatemEsmat]

Awesome, Thanks a lot❤

[@AccessAnalytic]

You're welcome

[@tomstrack42]

Excellent!

[@AccessAnalytic]

Cheers

[@mnowako]

Great one, thanks!

[@AccessAnalytic]

Cheers Mariusz

[@baskis69]

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

[@AccessAnalytic]

Thank you 😄

[@lorenzoladejobi8701]

Very educative!

[@AccessAnalytic]

Thank you

[@entertainmentgalaxy971]

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]

Cheers

[@thebhaskarjoshi]

Loved it.

[@AccessAnalytic]

Thanks Bhaskar

[@hassan06ny]

Thank you!

[@AccessAnalytic]

You’re welcome

[@alexrosen8762]

Super smart and useful

[@AccessAnalytic]

Cheers Alex

[@nazarkamal8831]

Awesome 👍👍👍

[@AccessAnalytic]

Thanks

[@cheikhhakim5864]

thank you sir

[@AccessAnalytic]

You’re welcome

[@juancarlosleytonfernandez9146]

Genius!!!!!

[@AccessAnalytic]

Thank you 🤗

[@Bhavik_Khatri]

Nice tricks

[@AccessAnalytic]

Thanks Bhavik

[@youssefelghandour3008]

Bahebak❤

[@kebincui]

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❤👍

[@mdwasitalinayyar]

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

[@AccessAnalytic]

Nice one 😀

[@IMAS7512]

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

[@AccessAnalytic]

That sounds like an error in the source excel file.

[@pascaljoly5752]

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]

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]

@@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]

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]

Sounds fine to me