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")
Thank you so so much! I can’t believe how brilliantly this worked, I’ve been trying to sort this for a while with no luck - and what a lovely bonus tip to include too! Thank you!
@@AccessAnalytic Of course! Once again, thank you so much, I knew there had to be some way around it but whatever I tried wasn’t working so it was starting to feel a little hopeless. You have such a calming voice and teaching manner, which is honestly brilliant for those of us who struggle to understand all the ins and outs. I’m very grateful, and I’m sure so many others are too! :)
@@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 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.
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"))
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.
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 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.
#"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?
awesome
Cheers Bernat
The tips are great and so is the guitar music at the end! 🎸
Cheers!
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")
Nice suggestion Bill
Thank you so so much! I can’t believe how brilliantly this worked, I’ve been trying to sort this for a while with no luck - and what a lovely bonus tip to include too! Thank you!
You're welcome - I really appreciate you letting me know
@@AccessAnalytic Of course! Once again, thank you so much, I knew there had to be some way around it but whatever I tried wasn’t working so it was starting to feel a little hopeless. You have such a calming voice and teaching manner, which is honestly brilliant for those of us who struggle to understand all the ins and outs. I’m very grateful, and I’m sure so many others are too! :)
Excellent tip to deal with annoying error in pivoting a column. Thank you Wyn
You’re welcome Kebin
Believe me today I needed it and fortunately I found my solution in this video.. thank you very much sir
You’re very welcome
Great tip, Wyn! You always pleasantly surprise us with these weekly tips. ❤
Thanks Krishan 🤗
very helpful, you've saved my day - thanks a lot, and the bonus tip was very helpful as well👍👌😊
Glad to help
Sir, usted no sabe como lo amo en este momento!
Glad it was useful
always a pleasure learning from you on Saturday.
😀glad to teach ( and learn ) Abdul
Very useful! I needed this for a two-column data set as an alternative to my complicated let/drop/reduce/lambda formula.
I appreciate you taking the time to let me know you found it useful
anything is better than lambda. you hand offv a spreadsheet to anyone with that function all you get is stares.
@@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.
@@TopBam its only intuitive to the most powerful of power users. i tried explaining the function in a shared spreadsheet that went absolutely nowhere.
@@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.
Wowww.. just what I needed ..thanks a lot 🙂
You’re welcome. I appreciate you taking the time to let me know you found it useful
Thank you for clear explanation.
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"))
Cheers
Brilliant, exact what I want to learn! Thank you
Thanks for letting me know it was useful 😀
Nice video. Many thanks
You’re welcome
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.
You’re welcome 😀
Very helpful video, thanks Wyn!
You’re welcome Chris. Thanks for taking the time to leave a kind comment
grate solution. thank you
Glad it helped
Awesome! Very powerful.
Absolutely
so simple and so useful !!!
Glad to help
Nice tricks
Thanks Bhavik
Awesome, Thanks a lot❤
You're welcome
Excellent!
Cheers
Very educative!
Thank you
thank you sir
You’re welcome
Nice trick... you're a power query wizard¡¡¡ ;)
Thank you 😄
Super smart and useful
Cheers Alex
Great one, thanks!
Cheers Mariusz
Genius!!!!!
Thank you 🤗
Thank you!
You’re welcome
Bahebak❤
Awesome 👍👍👍
Thanks
Loved it.
Thanks Bhaskar
I concatenate 01-jan-2023 to make a custom column of date.
The. Sort by that custom date column
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?
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
@@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.
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?
Sounds fine to me
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
Yep, sure does but you get a single row for every entry so it looks pretty messy
@@AccessAnalytic Thanks Wyn for your quick clarification❤👍
DataFormat.Error: Invalid cell value '#NAME?'. How to solve this error
That sounds like an error in the source excel file.