Unpivoting multiple measures indefinitely repeated

[maclura]

Hi,

I found several posts on how to unpivot in the most diverse situation, but not in this one.

I have a table like the below

where the "Code", "Type" and "Score" triplets can be repeated an undefined number of time (the same for all rows).

What is the smartest way to transform this tabe in the following?

Thank you for any hint

[mahoneypat · Accepted solution]

Please see this video for one way to handle the transform for repeating columns like this.

(11) Faster Data Transformations with List/Record M Functions - YouTube

Pat

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nQxVNJRMjTSNTDWNTIwAnGcQISnXwiQNAFxjeBcYxDXGM41UorVAZlghNUEP0cgAdMP5xjDORC9xlj1ukZAzIfqhnBNYPohXFOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Date Sent" = _t, Code1 = _t, Type1 = _t, Score1 = _t, Code2 = _t, Type2 = _t, Score2 = _t, Code3 = _t, Type3 = _t, Score3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date Sent", type date}, {"Code1", type text}, {"Type1", type text}, {"Score1", Int64.Type}, {"Code2", type text}, {"Type2", type text}, {"Score2", Int64.Type}, {"Code3", type text}, {"Type3", type text}, {"Score3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Date Sent"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Attribute.2"})
in
    #"Removed Columns"

Hope this helps.

The headings of the source dataset should be

[mahoneypat · Accepted solution]

Please see this video for one way to handle the transform for repeating columns like this.

(11) Faster Data Transformations with List/Record M Functions - YouTube

Pat