Hi Mike. Just found this lesson. Awesome tricks! I first solved by unpivoting one table and then converting the steps to a custom function to then be applied to all the tables. It worked, but not needed, given what you taught me here. And the date parsing trick when combining from one big table is genius! Thanks to you (and Bill S.) for this super useful tutorial. One example at a time, I'm building my Power Query knowledgebase :)) Thumbs up!!
Hi, I already saw this video and it seems so interesting. I have an issue with an Excel file and I don’t find a real solution. I’m trying your solution but I’m still with a HUGE INTERROGATION SIGN! I hope you read this comment.
You are simply awesome ! Easy to understand and follow along... I'm a financial controller trainee and your videos really help ! You should do some on "advanced excel graphs" such as Marimekkos... Thank you very much for all these tips !!
Well, I am not going to do videos on that sort of chart, but if you do cash flow analysis, I have a 110 video playlist of Financial Videos here: th-cam.com/play/PL90E1F26C7B85E78F.html
This is fascinating, thanks for sharing! Can't wait to try some of these techniques! Plus, this demonstrates the utility of learning M as opposed to relying only on the GUI to click your way through.
Hi Mike, Great Video and your style of explaining is fantastic. I have similar workbooks but there are 3 more merged rows in each group denoting region, sub region and Department. Can you guide how to unpivot them also along with Products.
Thanks Mike. What luck! I had a use for this and then this video appeared. [I discovered that you can just click on that ABC123 button to change the Data Type.]
Great video Mike! Always learn a lot from you. At the minute (21:59) mark to uncheck "Column31". This is the column that help us group. What if the source table added or deleted a column? Then, Column31 would be no longer. Currently, "Column31" is hard-coded into the M code. Could this be dynamic?
Sir, how could we change the data format from row to columns in power query? I mean unpivot columns to pivot columns. Your help in this regard is highly appreciated.
a question here: can you make follow up video on this? lets say we needed this to be grouped by product, and after we would need to create new column with dates for this product from minimum to the maximum date the product was sold. I had similar problem and could not figure out how to place min / max date in 1 column.
you know what :) i added this step: = Table.Group(#"Changed Type2", {"Product"}, {{"Count", each List.Sum([Units]), type text}, {"sss", each "From " & Text.From(List.Min([Date])) & " To " & Text.From(List.Max([Date])), type table [Date=nullable text, Product=text, Units=text]}}) at the end of all ur steps and it totally worked! and that's just after watching and doing some o ur excersies! I though I wont be able to do it, altho that problem occurred a week ago. Thank you Mr. Mike "ExcelIsFun" :)gr8 tutorials! I'm glad I found this channel!
22:10 would it be better to First transform into a date, then everything else what is not a date would be an error, and we could filter out errors. this in my opinion would be safer option.
You would list the column names. If you were using the user interface to Unpivot, you would select columns, then Right-click and Unpivot Other Columns. If you are typing them out, type them out as a list.
Mike, Quick Question please if you can answer? I am having a hard time with Unpivoting and getting the results I want. For example in your case one column is Date and the other is Units. Let us say that I want to show for Sept or any other Single Month the Units (Let us change to Units sold % and Now when I create a Pivot Table, I can not change Units to % for Sept. It changes for all months. How do I achieve this result that I should be able to show % or any other format for a single Month. I tried to change at table level as well as Pivot Table Level but it is not working. (My % is based on 2 columns). Please advise? Thanks
What makes the order of the columns (Product/Date, Date, Units) in minute (9:31) differ than its order in minute (10:50) to be (Date, Product/Date, Units) although we have not changed the order of these columns?
Minute (9:31) is the order we determined by UnPivoting. Minute (10:50) is a Filter Drop-Down List, and those are always sorted unique lists. Filter Drop-downs are the same everywhere too: Excel, Power Query, Power Pivot and so on: always a sorted unique list.
Hi Mike. Just found this lesson. Awesome tricks! I first solved by unpivoting one table and then converting the steps to a custom function to then be applied to all the tables. It worked, but not needed, given what you taught me here. And the date parsing trick when combining from one big table is genius! Thanks to you (and Bill S.) for this super useful tutorial. One example at a time, I'm building my Power Query knowledgebase :)) Thumbs up!!
You are just amazing. Sir kindly explain formulae writing in Power query. Please sir.
Hi, I already saw this video and it seems so interesting. I have an issue with an Excel file and I don’t find a real solution. I’m trying your solution but I’m still with a HUGE INTERROGATION SIGN! I hope you read this comment.
You are simply awesome ! Easy to understand and follow along... I'm a financial controller trainee and your videos really help !
You should do some on "advanced excel graphs" such as Marimekkos... Thank you very much for all these tips !!
Well, I am not going to do videos on that sort of chart, but if you do cash flow analysis, I have a 110 video playlist of Financial Videos here:
th-cam.com/play/PL90E1F26C7B85E78F.html
This is fascinating, thanks for sharing! Can't wait to try some of these techniques! Plus, this demonstrates the utility of learning M as opposed to relying only on the GUI to click your way through.
You are welcome for the fascinating share, David Taylor!!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Hi Mike, Great Video and your style of explaining is fantastic. I have similar workbooks but there are 3 more merged rows in each group denoting region, sub region and Department. Can you guide how to unpivot them also along with Products.
wow ...great work ...now need to implement and master it. Thank you.
You are welcome!
Awesome video. Thank you.
You are welcome!
Thanks Mike. What luck! I had a use for this and then this video appeared. [I discovered that you can just click on that ABC123 button to change the Data Type.]
You are welcome! Thanks for the "hot tip" about clicking on ABC123 icon!!!
Also noticed you right click to on your Workbook Connection then click Edit....you can just double click and it will open the Editor.
Yes, that is a hot tip!!! Thanks!
Thank you Mike for this great video !!
Bravo! Thank you for your lessons!
You are welcome!
Amazing... thank you for this video... 🙏🏻
Thank you very much. Could you please elaborate {List syntax}?
Great video Mike! Always learn a lot from you. At the minute (21:59) mark to uncheck "Column31". This is the column that help us group. What if the source table added or deleted a column? Then, Column31 would be no longer. Currently, "Column31" is hard-coded into the M code. Could this be dynamic?
Great job,thanks.
You are welcome !
Great explanation!
Glad you like it! : )
Brilliant Mike
This is amazing. Thanks!
Is there any tutorial on data format to convert from rows to column I mean unpivot to pivot?
well done!
Sir, how could we change the data format from row to columns in power query? I mean unpivot columns to pivot columns. Your help in this regard is highly appreciated.
a question here: can you make follow up video on this? lets say we needed this to be grouped by product, and after we would need to create new column with dates for this product from minimum to the maximum date the product was sold.
I had similar problem and could not figure out how to place min / max date in 1 column.
you know what :)
i added this step: = Table.Group(#"Changed Type2", {"Product"}, {{"Count", each List.Sum([Units]), type text}, {"sss", each "From " & Text.From(List.Min([Date])) & " To " & Text.From(List.Max([Date])), type table [Date=nullable text, Product=text, Units=text]}})
at the end of all ur steps and it totally worked! and that's just after watching and doing some o ur excersies! I though I wont be able to do it, altho that problem occurred a week ago.
Thank you Mr. Mike "ExcelIsFun" :)gr8 tutorials! I'm glad I found this channel!
Thanks Mike
You are welcome!
22:10 would it be better to First transform into a date, then everything else what is not a date would be an error, and we could filter out errors. this in my opinion would be safer option.
Thanks Master Mike!!
You are welcome, Power Query Artist and Poet Bill Szysz!!!! : )
Mr. Girvin,
It is giving me an error: "Product/Date" column does not exist; after loading.
That.. is a mazing !
the best
Glad you like it!!!!
THX alot Mister!
If there would be more than one column which should not get unpivot, do you just add , {"TableName2"}, ...
You would list the column names. If you were using the user interface to Unpivot, you would select columns, then Right-click and Unpivot Other Columns. If you are typing them out, type them out as a list.
List format like this: {"Date", "Product", "Region"} , as an example.
Great Video. Is there a book with power query functions?
No, just Microsoft's web site:
msdn.microsoft.com/en-us/library/mt253322.aspx
Thanks mike
You are welcome!
Mike, Quick Question please if you can answer? I am having a hard time with Unpivoting and getting the results I want. For example in your case one column is Date and the other is Units. Let us say that I want to show for Sept or any other Single Month the Units (Let us change to Units sold % and Now when I create a Pivot Table, I can not change Units to % for Sept. It changes for all months. How do I achieve this result that I should be able to show % or any other format for a single Month. I tried to change at table level as well as Pivot Table Level but it is not working. (My % is based on 2 columns). Please advise? Thanks
amazing! Thanks for the video.
the link below the video takes me to the the page where all your contect is, i cant find files used in this vdo. kindly help.
What makes the order of the columns (Product/Date, Date, Units) in minute (9:31) differ than its order in minute (10:50) to be (Date, Product/Date, Units) although we have not changed the order of these columns?
Minute (9:31) is the order we determined by UnPivoting.
Minute (10:50) is a Filter Drop-Down List, and those are always sorted unique lists. Filter Drop-downs are the same everywhere too: Excel, Power Query, Power Pivot and so on: always a sorted unique list.
It is not about the Filter, it is about the order of the three columns
This is a lot like Power BI
8-)
: )