Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Transform multiple columns into a single row

I have data which is categorized as attributes of New and Used vehicles ie.(New SRPIS, Used SRPIS, New VDPS Used VDPS); by date and client.  I need to perserve the date and client but transform the a...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    This M code works

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZBLDoUgDEW38sJYk9sv9W3FuP9tSKuGGQxoIb2HE86zwXfwzmD8gD/QtkbIRZy9GMcoIWPLs7v0GrG6uLZBsDUBlfCPYBSaIxSToEsC05FhoZfAYUmwvKQHIEsAeRxlUoHRqJVJnwa8NtB6rOsLUHhmj5gGtAZwfZjYZ8CexfEYXDc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MODIFIED_DATE = _t, CUSTOMER_ID = _t, NEW_SRPIS = _t, NEW_VDPS = _t, NEW_EMAILS = _t, USED_SRPIS = _t, USED_VDPS = _t, USED_EMAILS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MODIFIED_DATE", type datetime}, {"CUSTOMER_ID", Int64.Type}, {"NEW_SRPIS", Int64.Type}, {"NEW_VDPS", Int64.Type}, {"NEW_EMAILS", Int64.Type}, {"USED_SRPIS", Int64.Type}, {"USED_VDPS", Int64.Type}, {"USED_EMAILS", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"MODIFIED_DATE", "CUSTOMER_ID"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

    Hope this helps.