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
@krishanphophalia9303 Жыл бұрын
Great tip, Wyn! You always pleasantly surprise us with these weekly tips. ❤
@AccessAnalytic Жыл бұрын
Thanks Krishan 🤗
@sebastianschick19895 ай бұрын
very helpful, you've saved my day - thanks a lot, and the bonus tip was very helpful as well👍👌😊
@AccessAnalytic5 ай бұрын
Glad to help
@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
@abdulsaidmkamara7305 Жыл бұрын
always a pleasure learning from you on Saturday.
@AccessAnalytic Жыл бұрын
😀glad to teach ( and learn ) Abdul
@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
@MoRo90836 ай бұрын
Sir, usted no sabe como lo amo en este momento!
@AccessAnalytic6 ай бұрын
Glad it was 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
@TopBam6 ай бұрын
Very useful! I needed this for a two-column data set as an alternative to my complicated let/drop/reduce/lambda formula.
@AccessAnalytic6 ай бұрын
I appreciate you taking the time to let me know you found it useful
@dougmphilly3 ай бұрын
anything is better than lambda. you hand offv a spreadsheet to anyone with that function all you get is stares.
@TopBam3 ай бұрын
@@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.
@dougmphilly3 ай бұрын
@@TopBam its only intuitive to the most powerful of power users. i tried explaining the function in a shared spreadsheet that went absolutely nowhere.
@TopBam3 ай бұрын
@@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.
@user-en3pc7ko8e5 ай бұрын
Awesome! Very powerful.
@AccessAnalytic5 ай бұрын
Absolutely
@shubhabratadey10 ай бұрын
Nice video. Many thanks
@AccessAnalytic10 ай бұрын
You’re welcome
@ksowjanya448810 ай бұрын
Thank you for clear explanation.
@AccessAnalytic10 ай бұрын
You're welcome
@behzadghah Жыл бұрын
grate solution. thank you
@AccessAnalytic Жыл бұрын
Glad it helped
@109zxg8 ай бұрын
Brilliant, exact what I want to learn! Thank you
@AccessAnalytic8 ай бұрын
Thanks for letting me know it was useful 😀
@HatemEsmat2 ай бұрын
Awesome, Thanks a lot❤
@AccessAnalytic2 ай бұрын
You're welcome
@tomstrack42 Жыл бұрын
Excellent!
@AccessAnalytic Жыл бұрын
Cheers
@gsbertho10 ай бұрын
so simple and so useful !!!
@AccessAnalytic10 ай бұрын
Glad to help
@baskis69 Жыл бұрын
Nice trick... you're a power query wizard¡¡¡ ;)
@AccessAnalytic Жыл бұрын
Thank you 😄
@entertainmentgalaxy9719 ай бұрын
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"))
@AccessAnalytic9 ай бұрын
Cheers
@chrism9037 Жыл бұрын
Very helpful video, thanks Wyn!
@AccessAnalytic Жыл бұрын
You’re welcome Chris. Thanks for taking the time to leave a kind comment
@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 😀
@Bhavik_Khatri Жыл бұрын
Nice tricks
@AccessAnalytic Жыл бұрын
Thanks Bhavik
@lorenzoladejobi8701 Жыл бұрын
Very educative!
@AccessAnalytic Жыл бұрын
Thank you
@cheikhhakim5864 Жыл бұрын
thank you sir
@AccessAnalytic Жыл бұрын
You’re welcome
@nazarkamal88317 ай бұрын
Awesome 👍👍👍
@AccessAnalytic7 ай бұрын
Thanks
@juancarlosleytonfernandez9146 Жыл бұрын
Genius!!!!!
@AccessAnalytic Жыл бұрын
Thank you 🤗
@alexrosen8762 Жыл бұрын
Super smart and useful
@AccessAnalytic Жыл бұрын
Cheers Alex
@mnowako Жыл бұрын
Great one, thanks!
@AccessAnalytic Жыл бұрын
Cheers Mariusz
@hassan06ny Жыл бұрын
Thank you!
@AccessAnalytic Жыл бұрын
You’re welcome
@thebhaskarjoshi Жыл бұрын
Loved it.
@AccessAnalytic Жыл бұрын
Thanks Bhaskar
@youssefelghandour3008 Жыл бұрын
Bahebak❤
@mdwasitalinayyar Жыл бұрын
I concatenate 01-jan-2023 to make a custom column of date. The. Sort by that custom date column
@AccessAnalytic Жыл бұрын
Nice one 😀
@IMAS751211 ай бұрын
DataFormat.Error: Invalid cell value '#NAME?'. How to solve this error
@AccessAnalytic11 ай бұрын
That sounds like an error in the source excel file.
@pascaljoly57527 ай бұрын
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?
@AccessAnalytic7 ай бұрын
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
@pascaljoly57527 ай бұрын
@@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.
@pascaljoly57527 ай бұрын
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?
@AccessAnalytic7 ай бұрын
Sounds fine to me
@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❤👍