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.
Hi guys...
I'm trying to extract all data from this column keeping all data in one column only, separated by comma.
Solved! Go to Solution.
Hi @Anonymous
There isn't one function to do this in a quick step. This below will walk you through the logic of it. Put this M code in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUosTklTitWJVnJC5jgjc0DKKioq4KpgbGcYOxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Test", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Data"}, {{"All Rows", each _, type table [Data=text, Test=text]}}),
#"Added Just the Test Column as Table" = Table.AddColumn(#"Grouped Rows", "Test Column", each Table.SelectColumns([All Rows], "Test")),
#"Added Transposed Table" = Table.AddColumn(#"Added Just the Test Column as Table", "Transposed Table", each Table.Transpose([Test Column])),
#"Added Table as a List" = Table.AddColumn(#"Added Transposed Table", "Table as a List", each Table.ColumnNames([Transposed Table])),
#"Added Concatenated Fields" = Table.AddColumn(#"Added Table as a List", "Concatenated Fields", each Table.CombineColumns([Transposed Table],[Table as a List], Combiner.CombineTextByDelimiter(","), "Test")),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Concatenated Fields", "Concatenated Fields", {"Test"}, {"Test"})
in
#"Expanded Custom.2"
Kind of the long way, but this is easier to read:
Start with step 1. The first few steps were just me slapping in some data to get a nested table with a single column really quickly.
This should work with multiple rows, not just two.
If you need further help, please post back with sample data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous
There isn't one function to do this in a quick step. This below will walk you through the logic of it. Put this M code in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUosTklTitWJVnJC5jgjc0DKKioq4KpgbGcYOxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, Test = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Test", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Data"}, {{"All Rows", each _, type table [Data=text, Test=text]}}),
#"Added Just the Test Column as Table" = Table.AddColumn(#"Grouped Rows", "Test Column", each Table.SelectColumns([All Rows], "Test")),
#"Added Transposed Table" = Table.AddColumn(#"Added Just the Test Column as Table", "Transposed Table", each Table.Transpose([Test Column])),
#"Added Table as a List" = Table.AddColumn(#"Added Transposed Table", "Table as a List", each Table.ColumnNames([Transposed Table])),
#"Added Concatenated Fields" = Table.AddColumn(#"Added Table as a List", "Concatenated Fields", each Table.CombineColumns([Transposed Table],[Table as a List], Combiner.CombineTextByDelimiter(","), "Test")),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Added Concatenated Fields", "Concatenated Fields", {"Test"}, {"Test"})
in
#"Expanded Custom.2"
Kind of the long way, but this is easier to read:
Start with step 1. The first few steps were just me slapping in some data to get a nested table with a single column really quickly.
This should work with multiple rows, not just two.
If you need further help, please post back with sample data.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.