Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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": {} }
Solved! Go to 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
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |