Forum Discussion
Column Hierarchy Transformation
Hi all!
I hope you can guide me in the right direction.
I have a dataset that looks like this:
Sample
I want to use this dataset in multiple graphs, and for that I want the source data to look as follows. I have played around with PowerQuery and can't seem to get the right format. As long as I can replicate the steps in PowerBI to transform the data, anything goes. Hopefully some of you have encountered a similar problem already.
I copy two tables that are both acceptable and desired outcomes.
Desired Result Also desired result
I attach a link to the sample and results file here:
Hello! I loaded your data into Power BI, then went to Power Query to transform:
The first step I did was to select the Kitchen and Service columns and unpivoted the columns which added Kitchen and Service to the rows and added a value column.
Next, I removed the value column (you are better off creating a total measure in Power BI) and moved the Attribute column to after business
Next, I selected all of the columns after Attribute and unpivoted columns
And, finally, I removed the .1 in Attribute.1 column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIyBBIgDEEgljGECaLMlGJ1oOqMEApA2AiJBiuOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Business = _t, Efficiency = _t, Economy = _t, Speed = _t, Kitchen = _t, Efficiency.1 = _t, Economy.1 = _t, Speed.1 = _t, Service = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Business", type text}, {"Efficiency", Int64.Type}, {"Economy", Int64.Type}, {"Speed", Int64.Type}, {"Kitchen", Int64.Type}, {"Efficiency.1", Int64.Type}, {"Economy.1", Int64.Type}, {"Speed.1", Int64.Type}, {"Service", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Business", "Efficiency", "Economy", "Speed", "Efficiency.1", "Economy.1", "Speed.1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Region", "Business", "Attribute", "Efficiency", "Economy", "Speed", "Efficiency.1", "Economy.1", "Speed.1"}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Region", "Business", "Attribute"}, "Attribute.1", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1",".1","",Replacer.ReplaceText,{"Attribute.1"})
in
#"Replaced Value"
5 Replies
Anonymous ,
Please review attached pbix.
Trust you can follow the PQ steps.Totals are probably best left off of your Fact table and use Measures to create these.
Regards,
Hello! I loaded your data into Power BI, then went to Power Query to transform:
The first step I did was to select the Kitchen and Service columns and unpivoted the columns which added Kitchen and Service to the rows and added a value column.
Next, I removed the value column (you are better off creating a total measure in Power BI) and moved the Attribute column to after business
Next, I selected all of the columns after Attribute and unpivoted columns
And, finally, I removed the .1 in Attribute.1 column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIyBBIgDEEgljGECaLMlGJ1oOqMEApA2AiJBiuOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Business = _t, Efficiency = _t, Economy = _t, Speed = _t, Kitchen = _t, Efficiency.1 = _t, Economy.1 = _t, Speed.1 = _t, Service = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Business", type text}, {"Efficiency", Int64.Type}, {"Economy", Int64.Type}, {"Speed", Int64.Type}, {"Kitchen", Int64.Type}, {"Efficiency.1", Int64.Type}, {"Economy.1", Int64.Type}, {"Speed.1", Int64.Type}, {"Service", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Business", "Efficiency", "Economy", "Speed", "Efficiency.1", "Economy.1", "Speed.1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Region", "Business", "Attribute", "Efficiency", "Economy", "Speed", "Efficiency.1", "Economy.1", "Speed.1"}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Region", "Business", "Attribute"}, "Attribute.1", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1",".1","",Replacer.ReplaceText,{"Attribute.1"})
in
#"Replaced Value"You're very welcome!
