Hey Chandeep.. inspired by your lesson, came up with the following to have definitive control over the columns to unpivot: 1. Make a second data table and name it: ColumnsToUnpivot; in the table, enter the exact column names that you want to unpivot (in your example: Jan, Feb, etc); load as connection only; convert to a list 2. Load Table1 as connection only; make a Reference to Table1 and rename it: Table1Ref 3. In Table1Ref do the formula: = Table.ColumnNames(Table1); convert to Table 4. Add Custom Column with formula: = Table.AddColumn(#"Converted to Table", "Custom", each List.Contains(ColumnsToUnpivot,[Column1])); filter for FALSE; drill down on Column1; rename as ColumnNames 5. Add step with: = Table1 6. Unpivot step with: = Table.UnpivotOtherColumns(Custom1, ColumnNames, "Month", "Value"); close and load to new or existing sheet I learned that referencing the Table1 query is necessary to avoid the dreaded Formula.Firewall error. Loading a specific list of column names that you want to unpivot puts you in complete control of the outcome, no matter what other columns are inserted into the original data table. List.Contains() does the magic to allow you to filter to exactly what you want from your specified list of column names. This was a great learning experience for me, inspired by your video lesson. So, thought I would share it back for anyone interested. Thanks again for all your great videos and resource. I always learn something new at Goodly :)) Thumbs up!!
I was working through this query as part of my learning, using the same logic as the video or instead finding columns to unpivot you can do it in one step using list select, you can access the has error field if you wrap your logical test in brackets and use the field access operator, so the result returns true or false, which in list select true will select the item here is the part of the code: List Select List Argument Table Column Names Selection condition each ( try Date From ) [ Has Error ] That should get you all the columns that dont convert to a date or with not all the columns that do in one step, then just use the result in unpivot columns
Hi Chandeep! Thank you so much for this knowledgeable tutorial, I learned a lot to watch this tutorial. Please take good care of yourself & your family, stay safe & healthy. Take care Thanks Kashif
Thanks. The following code also works with few steps let Source = Excel.CurrentWorkbook(){[Name="PivotedTable"]}[Content], // Get the Table content /* Pivot criteria is to keep only the month columns Table column names are tested to convert them to type date if fails that is not included in the column names */ PivotCriteria = List.Select(Table.ColumnNames(Source), each try Value.Type(Date.FromText("2002/"& _ &"/01"))=type date otherwise false), UnpivotedTable = Table.Unpivot(Source, PivotCriteria, "Month", "Qty") in UnpivotedTable
Hi Chandeep. Awesome post! Love tricks to make procedures dynamic and I've wondered how to solve this exact problem. Thanks for sharing the technique. Will definitely use it in the future in my work :)) Thumbs up!!
Hey Chandeep, Thank you for your all videos, I've learnt lot. Actually I am looking for your help as I am creating a rental database of rental properties where we are paying rents to landlords. So below are some agreement details where I wanted to create a monthly Rent statement that to be paid to landlord Example - - Landlord Name - ABC - Agreement Period- 15 Aug 19 to 14 Aug 25 - Initial rent : 1000 - Escalation after 3 year : 15% - So after 3 year, rent will be - 1150 Output should be as per below: Sr No From To Rent 1 15 Aug 19 31 Aug 19 =1000/31x17 = 548.38 2 1 Sep 19 30 Sep 19 1000 3 1 Oct 19 31 Oct 19 1000 ... ... 36 1 Aug 22 31 Aug 22 = [1000/31x14] + [1150/31x17]= 1082.25 37 1Sep 22 30 Sep 22 1150 ... ... 61 1 Aug 15 14 Aug 25 =1150/31x14 = 519.35
i unpivot a selected columns to be use as lookup column for merge concatenated col query. after getting the result of that selected column how can i unpivot that one column to become a header adjacent to the right column.
If the following code works with but if we replace with = it does not works ..Why ? (List.Select(Table.ColumnNames(#"Removed Columns"), each _ "Item Name" and _ "Color" ) List.Select(Table.ColumnNames(#"Removed Columns"), each _ = "Item Name" and _ = "Color" ) (Refe.. Video . (Dynamic Unpivoting in Power Query)
How to adjust source file path or dynamically adj path where my working and source file placed...so when i share the file to someone then power query function properly ... pls advised
Chandeep, great video, i have an doubt, what if the column names are renamed like Jan Forecast becomes Jan Actual, here Jan is going to use to get the Date by which calendar table is linked. When we manually changes, the power query will given an error while refreshing. Do you have any suggestion to tackle this issue ?
Hey Vijay. See above my post for this solution. In short, you add another table as connection only with the specific column names you want to unpivot. Then you use List.Contains to filter the matches for the list that gets used in the Table.UnpivotOtherColumns step. The only complication is that Power Query does not like it when you refer to a Source made from a step inside the same Source. So, you have to trick it by landing the Source in one query and then referencing it as the Source for the second query where you actually perform the steps. Maybe Chandeep will to a video on this. Hope my comments help you. Good luck!
How about this unpivoting in a single statement? = Table.UnpivotOtherColumns(Source, List.FirstN(Table.ColumnNames(Source), each _ "Jan"), "Month", "Value")
Hey Chandeep.. inspired by your lesson, came up with the following to have definitive control over the columns to unpivot:
1. Make a second data table and name it: ColumnsToUnpivot; in the table, enter the exact column names that you want to unpivot (in your example: Jan, Feb, etc); load as connection only; convert to a list
2. Load Table1 as connection only; make a Reference to Table1 and rename it: Table1Ref
3. In Table1Ref do the formula: = Table.ColumnNames(Table1); convert to Table
4. Add Custom Column with formula: = Table.AddColumn(#"Converted to Table", "Custom", each List.Contains(ColumnsToUnpivot,[Column1])); filter for FALSE; drill down on Column1; rename as ColumnNames
5. Add step with: = Table1
6. Unpivot step with: = Table.UnpivotOtherColumns(Custom1, ColumnNames, "Month", "Value"); close and load to new or existing sheet
I learned that referencing the Table1 query is necessary to avoid the dreaded Formula.Firewall error. Loading a specific list of column names that you want to unpivot puts you in complete control of the outcome, no matter what other columns are inserted into the original data table. List.Contains() does the magic to allow you to filter to exactly what you want from your specified list of column names. This was a great learning experience for me, inspired by your video lesson. So, thought I would share it back for anyone interested. Thanks again for all your great videos and resource. I always learn something new at Goodly :)) Thumbs up!!
Awesome - EXACTLY what i needed to learn how to do. Many thanks Chandeep
Great to hear!
you are a genius, exactly what I was looking for!
Like wow. I learn something new on all your videos. You are the boss.
Thank you 💚
Just used this for the first time - simple and effective. Another tool in the arsenal. Thank you Chandeep!
Excellent video ! Thanks Chandeep. Very useful.
Glad you like it!
Nothing but the best my friend, good job 🙌🏻
Thank you !
I was working through this query as part of my learning, using the same logic as the video or instead finding columns to unpivot you can do it in one step using list select, you can access the has error field if you wrap your logical test in brackets and use the field access operator, so the result returns true or false, which in list select true will select the item here is the part of the code:
List Select
List Argument Table Column Names
Selection condition each ( try Date From ) [ Has Error ]
That should get you all the columns that dont convert to a date or with not all the columns that do in one step, then just use the result in unpivot columns
Good stuff in this one!
Hi Chandeep! Thank you so much for this knowledgeable tutorial, I learned a lot to watch this tutorial.
Please take good care of yourself & your family, stay safe & healthy.
Take care
Thanks
Kashif
Thanks a ton
So simple! And so useful!
Glad it was helpful!
Great, as usual 👏
Thanks. The following code also works with few steps
let
Source = Excel.CurrentWorkbook(){[Name="PivotedTable"]}[Content], // Get the Table content
/* Pivot criteria is to keep only the month columns
Table column names are tested to convert them to type date if fails that is not included in the column names */
PivotCriteria = List.Select(Table.ColumnNames(Source), each try Value.Type(Date.FromText("2002/"& _ &"/01"))=type date otherwise false),
UnpivotedTable = Table.Unpivot(Source, PivotCriteria, "Month", "Qty")
in
UnpivotedTable
Ajj ayuga beere suwaad bhut din ho gye matha marde nu....esse problem t
Thanks a lot, this video helped me to solve my problem :)
thank you for this sharing..really appreciate it.
Glad you like this KD!
Hi Chandeep. Awesome post! Love tricks to make procedures dynamic and I've wondered how to solve this exact problem. Thanks for sharing the technique. Will definitely use it in the future in my work :)) Thumbs up!!
Thanks Wayne! Glad you found it helpful.
Learning query through your video is quite simple.
Thanks for making it very easy to understand.
Glad to hear that
Awesome!!!
Thanks!!
Tremendous solution! Thank you Chandeep!
very helpful, thanks
It was great !😊
Thank you so much for this knowledgeable tutorial
Thank !! Thank !!
Excellent.
Thank you! Cheers!
Very interesting....
Love this!! Thanks!:)
Glad you love this!
Very nice 🎉❤
Excellent solution!!
amazing!
Hey Chandeep,
Thank you for your all videos, I've learnt lot.
Actually I am looking for your help as I am creating a rental database of rental properties where we are paying rents to landlords. So below are some agreement details where I wanted to create a monthly Rent statement that to be paid to landlord
Example -
- Landlord Name - ABC
- Agreement Period- 15 Aug 19 to 14 Aug 25
- Initial rent : 1000
- Escalation after 3 year : 15%
- So after 3 year, rent will be - 1150
Output should be as per below:
Sr No From To Rent
1 15 Aug 19 31 Aug 19 =1000/31x17 = 548.38
2 1 Sep 19 30 Sep 19 1000
3 1 Oct 19 31 Oct 19 1000
...
...
36 1 Aug 22 31 Aug 22 = [1000/31x14] + [1150/31x17]= 1082.25
37 1Sep 22 30 Sep 22 1150
...
...
61 1 Aug 15 14 Aug 25 =1150/31x14 = 519.35
Thank you! :)
Really interesting solution!
Excellent! Thank you! 👏
Glad you liked it!
Great video!
Thanks for the tutorial.I have query like how we can make the other columns (jan-dec) dynamic?My unpivot columns are from current month till X month.
How can we create patterns if the columns is lets say some measures which are other than dates or months?
i unpivot a selected columns to be use as lookup column for merge concatenated col query. after getting the result of that selected column how can i unpivot that one column to become a header adjacent to the right column.
Great tutorial Chandeep.. 👍💐
Thanks a lot 😊
Excellent job 👏👍👌
Thanks again!
if we add column after brand .....
If the following code works with but if we replace with = it does not works ..Why ?
(List.Select(Table.ColumnNames(#"Removed Columns"), each _ "Item Name" and _ "Color" )
List.Select(Table.ColumnNames(#"Removed Columns"), each _ = "Item Name" and _ = "Color" )
(Refe.. Video . (Dynamic Unpivoting in Power Query)
Great video as always thank you
💯👍
How to adjust source file path or dynamically adj path where my working and source file placed...so when i share the file to someone then power query function properly ... pls advised
Please see this - ua-cam.com/video/LHNVkiGlUbE/v-deo.html
@@GoodlyChandeep thanks sir
Chandeep, great video, i have an doubt, what if the column names are renamed like Jan Forecast becomes Jan Actual, here Jan is going to use to get the Date by which calendar table is linked. When we manually changes, the power query will given an error while refreshing. Do you have any suggestion to tackle this issue ?
Hey Vijay. See above my post for this solution. In short, you add another table as connection only with the specific column names you want to unpivot. Then you use List.Contains to filter the matches for the list that gets used in the Table.UnpivotOtherColumns step. The only complication is that Power Query does not like it when you refer to a Source made from a step inside the same Source. So, you have to trick it by landing the Source in one query and then referencing it as the Source for the second query where you actually perform the steps. Maybe Chandeep will to a video on this. Hope my comments help you. Good luck!
How about this unpivoting in a single statement?
= Table.UnpivotOtherColumns(Source, List.FirstN(Table.ColumnNames(Source), each _ "Jan"), "Month", "Value")
Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/