Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
I have a Power BI query, which has one column that has a textual list of key-value pairs like:
"Key1: Value1, Key2: Value2, Key3: Value3"
I would like to extent the existing table with three additional columns that hold the values:
Key1 | Key2 | Key3 |
Value1 | Value2 | Value3 |
Is there a simple way to do anything like this in M?
Solved! Go to Solution.
Thank you for the suggestion. I also played around a little with different possibilities, and in the end came up with this solution, which allows to extract arbitrary key-value pairs.
SplitByDelimiter = (table, column, extractcols) => let #"Changed Type" = Table.TransformColumnTypes(table,{{column, type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{column, Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), column), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", column, Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Keys", "Values"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Keys", Text.Trim, type text}, {"Values", Text.Trim, type text}}), #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(extractcols), "Keys", "Values"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"
Thank you for the suggestion. I also played around a little with different possibilities, and in the end came up with this solution, which allows to extract arbitrary key-value pairs.
SplitByDelimiter = (table, column, extractcols) => let #"Changed Type" = Table.TransformColumnTypes(table,{{column, type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{column, Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), column), #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", column, Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Keys", "Values"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter1",{{"Keys", Text.Trim, type text}, {"Values", Text.Trim, type text}}), #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(extractcols), "Keys", "Values"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"
For all searching for a more generic way to split an unknown number of key-value-pairs into columns, you can use this approach:
let
Table = ... //Removing everything outside "{}", might not be relevant for your use. #"Only data between {}" = Table.TransformColumns(Table, {{"KeyValuePairColumn", each Text.BetweenDelimiters(_, "{", "}"), type text}}), //Split the KeyValuePairColumn into a row for each Key-Value pair #"Split into rows" = Table.ExpandListColumn(Table.TransformColumns(#"Only data between {}", {{"KeyValuePairColumn", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "KeyValuePairColumn"), //Split Key and Value into separate columns #"Split KeyValue" = Table.SplitColumn(#"Split into rows", "KeyValuePairColumn", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Key", "Value"}), #"Change type" = Table.TransformColumnTypes(#"Split KeyValue",{{"Key", type text}, {"Value", type text}}), //Pivot Key into column-name and Value into its value PivotColumn = Table.Pivot(#"Change type", List.Distinct(#"Change type"[Key]), "Key", "Value") in PivotColumn
This Approach can be build without using Avanced editor, and wont break if the number of key-value-pairs changes.
Hi @mdahlwei
Try these steps
1) First split the Column by Delimiter ":"
2) Transpose the Table
3) Promote first row as header
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column.1", "Column.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column.1", type text}, {"Column.2", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Key1", type text}, {"Key2", type text}, {"Key3", type text}})
in
#"Changed Type2"
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
107 | |
68 | |
48 | |
44 | |
44 |