Forum Discussion
Re: Complex unpivot - column names as rows
AllisonKennedy , thanks for your answer, yes, there are more columns that should not be unpivoted, let's say that there are more attributes of the Programs, but these are shared among programs.
I would go to the DB owners as the last result, rather not.
18 Replies
Hi, I need your support in the following.
I have the table on the left and need to transform it into the one on the right.
I am able do to so using the following code and pivoting/unpivoting and filtering, but I am wondering if there is a better, more efficient way?
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name 1", type text}, {"Attribute Name 2", type text}, {"Attribute Value 1", Int64.Type}, {"Attribute Value 2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"PROGRAM", "Attribute Value 1", "Attribute Value 2"}, "Attribute", "Value"), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"PROGRAM", "Attribute", "Value"}, "Attribute.1", "Value.1"), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.AfterDelimiter(_, " ", 1), type text}}), #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Attribute.1", each Text.AfterDelimiter(_, " ", 1), type text}}), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text After Delimiter1", "Custom", each if [Attribute] = [Attribute.1] then true else false), #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Attribute.1", "Custom"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Value]), "Value", "Value.1", List.Sum) in #"Pivoted Column"One of the challenges is that the attribute names will be changing and new being added.
Additional, would this be easier to do in the source DB? I am connecting to a HIVE DB.
In the link below you will find the excel file of the example (I need to do this in Power BI).
https://1drv.ms/x/s!Aj1t-UWaJ-akgdJBHMs3zhpeYWEGQw?e=efS6jz
In advance thanks.
Regards,
Oscar
Hi Oscar - here's a link to a solution. I did something similar last week.
I agree with AllisonKennedy that because every record has null values for 2/3 of the values something more tabular is probably a better solution. But if you have to have it as in the picture it should be easy to pivot to get there.
Hi Oscar~
This looks like two tables placed side by side in one, so you can consider trying to split them into their separate tables and do an append. I'm not convinced you need the PIVOT in the Power Query - could just do this in the visualizations instead, but I have put it in since you asked.
You'll need three Queries with the following names for this to work, just need to update the Source step with your Excel filepath.
Table1 Attribute 1
let
Source = Excel.Workbook(File.Contents("C:\\Example Unpivot.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Table1_Table,{"PROGRAM", "Attribute Name 1", "Attribute Value 1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Attribute Name 1", "Attribute Name"}, {"Attribute Value 1", "Attribute Value"}})
in
#"Renamed Columns"Table1 Attribute 2
let
Source = Excel.Workbook(File.Contents("C:\Users\Allison.DESKTOP-TFHI78M\Downloads\Example Unpivot.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Table1_Table,{"PROGRAM", "Attribute Name 2", "Attribute Value 2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Attribute Name 2", "Attribute Name"}, {"Attribute Value 2", "Attribute Value"}})
in
#"Renamed Columns"Table1 All
let
Source = Table.Combine({#"Table1 Attribute 1", #"Table1 Attribute 2"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PROGRAM", type text}, {"Attribute Name", type text}, {"Attribute Value", type number}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Attribute Name"]), "Attribute Name", "Attribute Value", List.Sum)
in
#"Pivoted Column"AllisonKennedy , thanks, my biggest issue is that the real case has over 20 columns for Attribute name and the same number for the attribute value.
amitchandak thanks, but it doesn't really address the issue of simplifying the current query, where multiple columns hold multiple column names and the same for its values. Cheers!
- Oscar_Mtz_V
Yes, it can be done w/o going to DB owners. You can either continue with your current method of using unpivot, or try the select columns and append approach. I still also think you should save the last 'pivot' step for doing in the report view within the visualizations, unless you need it in Power Query for something else?
Are you able to share a sample table or Excel file with the actual column names (all of them)? If there's patterns in the names, we can create a custom function for this.
PS, I'm also going to move this post to Power Query forum to see if it gets any more visibility from the Power Query specialists. 🙂
