Extracting a list of key-value pairs into multiple columns

[mdahlwei]

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?

[mdahlwei · Accepted 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"

[Anonymous]

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.

[Zubair_Muhammad]

Hi @mdahlwei

Try these steps

1) First split the Column by Delimiter ":"

2) Transpose the Table

3) Promote first row as header

[Zubair_Muhammad]

@mdahlwei

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"