Advanced query code: pick top key name to table

[LasseL]

Dear all,

I am performing the ETL on document-sources and I have struggle transforming one column, namely "Production".

When I click expand, as you can see, I get my required value (guid) as a key and I am unable to find the method to transform the "Record" to the guid. I am looking into something along Table.Keys and Top 1, but I can't figure out how to code it in M.

Current query script is as follows:

let
    Source = Json.Document(Binary.Decompress(Web.Contents("https://" & Environment & "/v0a/" & "" & Workspace & "" & "/Integration/BusinessIntelligence/GetByFiletype/" & "Entity" & "?Compressed=true"), Compression.GZip)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "WorkflowId", "Locations", "ItemId", "ProjectReferenceId", "OrderReferenceId", "Status", "LogisticStatus", "Identifiers", "Quantity", "Dates", "Metrics", "QuickStatus"}, {"id", "WorkflowId", "Locations", "ItemId", "ProjectId", "OrderId", "Status", "LogisticStatus", "Identifiers", "Quantity", "Dates", "Metrics", "QuickStatus"}),
    #"Expanded Locations" = Table.ExpandRecordColumn(#"Expanded Column1", "Locations", {"Production"})
in
    #"Expanded Locations"

Any ideas?

Desired outcome is:

It might help by understanding the JSON source structure, here's a snap from one of the documents:

"Locations": {
"CurrentLocation": {
"ID": "93468a60-35e2-4d34-932d-ffca6308892a",
"Custom": null
},
"All": {
"93468a60-35e2-4d34-932d-ffca6308892a": {
"ID": "93468a60-35e2-4d34-932d-ffca6308892a",
"Custom": null
}
},
"Production": {
"93468a60-35e2-4d34-932d-ffca6308892a": {
"ID": "93468a60-35e2-4d34-932d-ffca6308892a",
"Custom": null
}
},
"Storage": {
"93468a60-35e2-4d34-932d-ffca6308892a": {
"ID": "93468a60-35e2-4d34-932d-ffca6308892a",
"Custom": null
}
},
"Shipping": {},
"Delivery": {}
}

[v-lili6-msft · Accepted solution]

hi, @LasseL

Here is the document for you to check if it could help you.

https://powerbi.microsoft.com/en-us/blog/how-to-expand-a-list-of-records-in-a-query/

https://community.powerbi.com/t5/Desktop/Expanding-records-from-JSON/td-p/340256

Best Regards,

Lin