Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
88 | |
85 | |
68 | |
68 | |
64 |
User | Count |
---|---|
209 | |
118 | |
116 | |
81 | |
74 |