cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mdahlwei Frequent Visitor
Frequent Visitor

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

Accepted Solutions
mdahlwei Frequent Visitor
Frequent Visitor

Re: Extracting a list of key-value pairs into multiple 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"

   

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Extracting a list of key-value pairs into multiple columns

Hi @mdahlwei

 

Try these steps

 

1) First split the Column by Delimiter ":"

2) Transpose the Table

3) Promote first row as header

Super User
Super User

Re: Extracting a list of key-value pairs into multiple columns

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

 

mdahlwei Frequent Visitor
Frequent Visitor

Re: Extracting a list of key-value pairs into multiple 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"

   

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 293 members 2,859 guests
Please welcome our newest community members: