Forum Discussion

mrbajana's avatar
mrbajana
Helper III
6 years ago
Solved

Pivot or Unpivot Tables

[ Spoiler ]
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi mrbajana 

    As tested, camargos88  and Rocco_sprmnt21 's suggestions are helpful.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYuxDoAwCET/hblLAf2ZpkNNqlMd/P/BO2Qx4eCFl2tNrnlLEcw5jw/WeAi9NBkAfv+XpoJqLGRDPEKlII2F7KktlJEsWx5vaqroeyrLtkrvLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t, col5 = _t, col6 = _t]),
    mesi = List.RemoveMatchingItems( Record.FieldValues(Source{0}),{""}),
    rtr = Table.Skip(Source,1),
    ph = Table.PromoteHeaders(rtr, [PromoteAllScalars=true]),
    n=List.Count(mesi),
    tabs=List.Accumulate({0..n-1},ph,(s,c)=> Table.CombineColumns(s,{cols{2*c}, cols{2*c+1}},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),mesi{c})),

    cols=Table.ColumnNames(ph),

    #"Added Index" = Table.AddIndexColumn(tabs, "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Attribute", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows1",{{"Value.1", "a"}, {"Value.2", "b"}})
in
    #"Renamed Columns"

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I'm sure there is a better way to do what you ask, but try to use a scheme like this pending a more suitable solution.

All the steps are done by GUI

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYm5EcAwDMN2Ye3GzwJ5ttCpSOL9Z4iIAjgeGKGupm4dBpEeZQuNmsM6DSK9vLPmtC6DSB/vqrms2yDSVuYP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [a1 = _t, a2 = _t, b1 = _t, b2 = _t, c1 = _t, c2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a1", Int64.Type}, {"a2", Int64.Type}, {"b1", type text}, {"b2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"c1", "c2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.2"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"a1", type text}, {"a2", type text}}, "it-IT"),{"a1", "a2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"b1", "b2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged.1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns2", {"Index"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}})
in
    #"Changed Type1"

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi mrbajana  waiting for you to respond to the request of camargos88 , which is also mine, I assume that you have a table of this structure:

     

     

    if so, a more general solution than the one already proposed is the following.

    Try it and let us know ...

     

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcy7DYAwDEXRXVynIWEBAltEKZwPVFCwf4GvkdDR1XNEKXLNR4LYd872x60vUUMRXaw0Go1qVKd69Du5wKawgW/qZ55HyPw6Z/Ct+TlZJtg77OBb9/NqucIx4ADfhtT6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t, col3 = _t, col4 = _t, col5 = _t, col6 = _t]),
    mesi = List.RemoveMatchingItems( Record.FieldValues(Source{0}),{""}),
    rtr = Table.Skip(Source,1),
    ph = Table.PromoteHeaders(rtr, [PromoteAllScalars=true]),
    n=List.Count(mesi),
    tabs=List.Accumulate({0..n-1},ph,(s,c)=> Table.CombineColumns(s,{cols{2*c}, cols{2*c+1}},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),mesi{c})),

    cols=Table.ColumnNames(ph),

    #"Added Index" = Table.AddIndexColumn(tabs, "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}, {"Value", Order.Ascending}})
in
    #"Sorted Rows"