Forum Discussion

TheInvoker's avatar
TheInvoker
Frequent Visitor
4 years ago
Solved

How to get value from JSON document record in M query (Power Connector)?

In M language (power connector), I download data and convert it to json like this json = Json.Document(source) I know for sure this json has 1 record like this {Count: 3} But If I return this, i...
  • jennratten's avatar
    4 years ago

    Hello - you were really close!

    You should be seeing a list of records to convert the list to a table of records, on the ribbon, in the List tab, you can click Convert To Table. Then you can click the button in the column header to expand records.  If you'd like to access one record with a script, you can do so by referencing the element in the list and then values of the record's field.

    If json is the name of the step in which you have a list containing one record:

    Access the first element in the list:

    json{0}

    Access the value of the record's first field (quotes are not needed - if the name includes certian special characters then the name would need to be enclosed in #" "):

    json{0}[Count]

     

    let
        Source = Table.ToRecords(Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t])),
        Source1 = Source{0},
        Id = Source1[Id]
    in
        Id