power query (M) create record from a text type column

5
votes

when retrieving data from a web based API into the Power BI, I did end up with a quoted record (text type) which I am unable to convert to a record. There must be a simple way to do this, unfortunately I was not able to find it.

So simply record = [A=1,B=2] is implicitly detected, if written like this record = "[A=1,B=2]" you end up with a text, which needs to be converted. What function can do this kind of conversion?

This is the result similar to the API, which will require transformation on the entire column.

let
    Source = {"[A=1, B=1, C=0, D=1]","[A=1, B=1, C=0, D=1]"},
    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table
1
powerbipowerquery

1 Answers

6
votes

Expression.Evaluate:

let
    Source = {"[A=1, B=1, C=0, D=1]","[A=1, B=1, C=0, D=1]"},
    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Evaluate = Table.AddColumn(Table, "Custom", each Expression.Evaluate([Column1]))
in
    Evaluate