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
Hi all!
I hope you can guide me in the right direction.
I have a dataset that looks like this:
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.
I attach a link to the sample and results file here:
Solved! Go to 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"
Proud to be a Super User! | |
Thank you for providing the pbix!
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"
Proud to be a Super User! | |
Thank you for the visibility on the steps! This was great.
You're very welcome!
Proud to be a Super User! | |
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,
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |