Pivot or Unpivot Tables

[mrbajana]

Spoiler

It is possible to convert table 1 to table 2 in PowerQuery. The first row of the months has combined cells. The objective is to be able to create table number 2 for better DataSet.

Thanks A Lot

[v-juanli-msft · Accepted solution]

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]

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]

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"

[camargos88]

Hi @mrbajana ,

Can you provide some data, so I can copy and try it ?