Column Hierarchy Transformation

[pbarillas]

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:

https://docs.google.com/spreadsheets/d/1E_KDznRJjhfMWCd_j8C1scr-Ti7Yjd_b/edit?usp=sharing&ouid=10932...

[pbarillas]

Thank you for providing the pbix! 

[audreygerred · Accepted solution]

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"

[pbarillas]

Thank you for the visibility on the steps! This was great. 

[audreygerred]

You're very welcome!

[rsbin]

@pbarillas ,

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,