Forum Discussion
Unpivoting clustered columns
Another possible method using your example table...
let
Source =
Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("LcyxCcBADATBVozix6C7csQH7yJcv7FW0cIGUxUZK968OyLuHOZhHuaTF9X033tVCEYwghGMYASjYTSMYNSMYQxjGMMYxjAexsMYxrH3Bw==", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true])
in type table [Id = _t, V1 = _t, V2 = _t, V3 = _t, A1 = _t, A2 = _t, A3 = _t, B1 = _t, B2 = _t, B3 = _t]
),
#"Unpivoted Other Columns" =
Table.UnpivotOtherColumns(
Source,
{"Id"},
"Category",
"Value"
),
#"Split Column by Character Transition" =
Table.SplitColumn(
#"Unpivoted Other Columns",
"Category",
Splitter.SplitTextByCharacterTransition(
(c) => not List.Contains({"0".."9"}, c), {"0".."9"}
),
{"Category.2", "Category"}
),
#"Grouped Rows" =
Table.Group(
#"Split Column by Character Transition",
{"Id", "Category"},
{{"_grouped", each _, type table [Id=nullable text, Attribute.1=nullable text, Attribute.2=nullable text, Value=text]}}
),
Custom1 =
Table.TransformColumns(
#"Grouped Rows",
{
"_grouped",
each
Table.Pivot(
Table.SelectColumns(
_,
{"Category.2", "Value"}
),
List.Distinct(
Table.SelectColumns(
_,
{"Category.2", "Value"}
)[Category.2]
),
"Category.2", "Value"
)
}
),
#"Expanded _grouped" =
Table.ExpandTableColumn(
Custom1,
"_grouped",
{"V", "A", "B"},
{"V", "A", "B"}
)
in
#"Expanded _grouped"
I'm seeing this part there:
Would it break that part, or something else, if the V columns sometimes have a trailing character like ":"?
Like this:
Like, the source is data from a SharePoint form and they messed up the back big time. They just expect me to do magic or something.
Right now it is splitting the column where it transitions from letter to number.
If there are trailing characters after the number it will 'break' the grouping step that follows.
It sounds like you may need to add a step (or two) after the split to clean the category column to allow for the grouping to occur correctly.The nasty thing with this is that it is a simplification as well. There are 40 columns per category and no guarantee that the field name remain the same :). I forsee a lot of trouble during the scheduled refreshes...
Like, as well, I don't understand how your code works, nor the one from community support. How am I supposed to trouble shoot something I don't understand? Like, I have sufficient experience with Power BI to have a rough understanding what it does so I can cherry pick of a few uncertainties, but that's it.
I think the concatenation solution from j_ocean might be the best as it is the simplest and thereby guarantees for me continuity. Like, I'll wait a bit for your response. Perhaps your code is really easy to understand and trouble shoot, but otherwise I'll mark the merge of the column and after unpivot splitting them again as the solution.
Hi DouweMeer
You can put the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcyxCcAwEATBVszHwqC7ch4FchGu31h70cIG012zRr3zPhHxyWZu5mY+86JK/XeNLuEIRzjCEY5wFEdxFEfHMY5xjGMc4xjHcRzHcVxrfQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, V1 = _t, #"V2:" = _t, V3 = _t, #"A1:" = _t, #"A2," = _t, A3 = _t, #"B1 " = _t, B2 = _t, B3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"V1", type text}, {"V2:", type text}, {"V3", type text}, {"A1:", type text}, {"A2,", type text}, {"A3", type text}, {"B1 ", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id"}, "Attribute", "Value"), #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute.1", "Attribute.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine( List.RemoveNulls( List.Transform( Text.ToList([Attribute.2]), each if Value.Is(Value.FromText(_), type number) then _ else null) ) )), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.1]), "Attribute.1", "Value"), #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Custom", Int64.Type}}) in #"Changed Type2"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
