Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am having an issue where rather than showing the ID multiple times i want to show ID once but the Type region and price to all appear on the same line rather than seperate please see example data
Solved! Go to Solution.
hi @Anonymous
For this error, it means you have more than one value in same Megerd ID for Type or Region or Price.
It like this:
So please adjust the Pivot function as below:
here is M code, you could try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzc8vyQAy/PKLwLSCUqwOREoBpygI++SXg8WM8IgF55ci6QaJeiQWpcA1xMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t, Type = _t, Region = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", Int64.Type}, {"Type", type text}, {"Region", type text}, {"Price", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Type", "Region", "Price"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Max)
in
#"Pivoted Column"
Regards,
Lin
Hi @Anonymous
If you are referring matrix visual layout?
Then you can switch off the stepped layout.
@Mariusz sorry bad explanation i need to do this in Power Bi in M Query. As these columns need to be filters, i have tried pivotting the data then unpivotting but i get an error as says there can't be multiples
Hi @Anonymous
Can you paste the sample from Excel into the body of this post?
Hi @Anonymous
Try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzc8vyQAyFMA4VgciDOL55RdBZZBFQdgnvxwsZoRHLDi/FEk3SNQjsSgFYVEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t, Type = _t, Region = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", Int64.Type}, {"Type", type text}, {"Region", type text}, {"Price", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Type", "Region", "Price"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
or see the attached
@Mariusz thank you for this but i am getting the following erro;
Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
[List]
This is down to duplicates but i need these to be in there, thats the main part i am having issue with
hi @Anonymous
For this error, it means you have more than one value in same Megerd ID for Type or Region or Price.
It like this:
So please adjust the Pivot function as below:
here is M code, you could try it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOzc8vyQAy/PKLwLSCUqwOREoBpygI++SXg8WM8IgF55ci6QaJeiQWpcA1xMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merged = _t, Type = _t, Region = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Merged", Int64.Type}, {"Type", type text}, {"Region", type text}, {"Price", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",null,Replacer.ReplaceValue,{"Type", "Region", "Price"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Merged"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Max)
in
#"Pivoted Column"
Regards,
Lin
Hi @Anonymous
Can you provide the data sample that includes duplicates?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |