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 to all,
I've the source table were the third column has multiple "VALUES" with semicolon separator. I need to a add a column for each "VALUE" in the third column with the name of the column that is exactly the "VALUE". The values reported in each column added is the value of the second column if the header column is present in that source table.
the two examples can explain better my goal.
Solved! Go to Solution.
Here is one way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrKz00FUuGJJalF1iGpidbO+dmpSrE60UpGQOGAxKLMYmRpsIwxUMQ3Py8xOR8klZkHVB8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, City = _t, Drinks = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"City", type text}, {"Drinks", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Drinks], ";")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Drinks"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "City", "City - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Custom]), "Custom", "City - Copy")
in
#"Pivoted Column"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is one way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrKz00FUuGJJalF1iGpidbO+dmpSrE60UpGQOGAxKLMYmRpsIwxUMQ3Py8xOR8klZkHVB8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, City = _t, Drinks = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"City", type text}, {"Drinks", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Drinks], ";")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Drinks"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Duplicated Column" = Table.DuplicateColumn(#"Expanded Custom", "City", "City - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Custom]), "Custom", "City - Copy")
in
#"Pivoted Column"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |