Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
pbarillas
New Member

Column Hierarchy Transformation

Hi all! 

 

I hope you can guide me in the right direction. 

I have a dataset that looks like this:

SampleSample

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 ResultDesired Result       Also desired resultAlso 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...

 

 

 

1 ACCEPTED SOLUTION
audreygerred
Super User
Super User

Hello! I loaded your data into Power BI, then went to Power Query to transform: 

audreygerred_0-1708532573880.png

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. 

audreygerred_1-1708532673920.png

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 

audreygerred_2-1708532755095.png

Next, I selected all of the columns after Attribute and unpivoted columns

audreygerred_3-1708532805985.png

And, finally, I removed the .1 in Attribute.1 column. 

audreygerred_4-1708532867920.png

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

5 REPLIES 5
pbarillas
New Member

Thank you for providing the pbix! 

audreygerred
Super User
Super User

Hello! I loaded your data into Power BI, then went to Power Query to transform: 

audreygerred_0-1708532573880.png

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. 

audreygerred_1-1708532673920.png

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 

audreygerred_2-1708532755095.png

Next, I selected all of the columns after Attribute and unpivoted columns

audreygerred_3-1708532805985.png

And, finally, I removed the .1 in Attribute.1 column. 

audreygerred_4-1708532867920.png

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

You're very welcome!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





rsbin
Super User
Super User

@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,

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.