Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to 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.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.