Forum Discussion

maclura's avatar
maclura
Icon for Resolver I rankResolver I
5 years ago
Solved

Unpivoting multiple measures indefinitely repeated

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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