Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mdahlwei
New Member

Extracting a list of key-value pairs into multiple columns

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:

 

Key1Key2Key3
Value1Value2Value3

 

Is there a simple way to do anything like this in M?

1 ACCEPTED SOLUTION
mdahlwei
New Member

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"

   

View solution in original post

4 REPLIES 4
mdahlwei
New Member

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
Not applicable

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
Community Champion
Community Champion

Hi @mdahlwei

 

Try these steps

 

1) First split the Column by Delimiter ":"

2) Transpose the Table

3) Promote first row as header


Regards
Zubair

Please try my custom visuals

@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"

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.