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, it shows 1 record like that. But as a test I changed it to return a number like 3 (hardcoded), and in power BI it showed just a value instead of a table. How can I return the value instead from the json document? Something like json[0]["Count"]?
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
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