Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |