Search code examples
powerbipowerquerypowerbi-desktopm

Power BI - Record or table column from comma separated and list

How does one create a record or table from a comma separated value and a list of data? The comma separated values are the keys and the list contains the data.

So far I have tried

= Record.FromList((Splitter.SplitTextByEachDelimiter({","})([columns])),[data])

enter image description here

Expanding out the data and splitting the columns results in this: enter image description here

Solution

  • Here you go.

    enter image description here

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStRJ0klWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [columns = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"columns", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "list", each {1,2,3}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each let a =  Text.Split([columns], ","),
b = Table.FromColumns({a, [list]})
in b),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"columns", "list"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Custom"