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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.