Forum Discussion
column formatting issues
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 ...
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
| Merged | Type | Region | Price |
| 1 | Smooth | ||
| 1 | North | ||
| 1 | Low | ||
| 2 | Low | ||
| 2 | South | ||
| 2 | Hard |
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
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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