Forum Discussion
Solved
Pivot or Unpivot Tables
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
Thanks A Lot
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.
4 Replies
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.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"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"
