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.
I'm working with the QuickBooks API but this could apply to other nested custom columns in a json API call. Is there a function that will expand the CustomFields for me on 1 line for n number of custom columns (vs 2 records here or potentially n custom columns).
I've been able to solve for this by adding custom columns, filter, then merge back into the original dataset but thought there might be a simpler way?
I've seen a few functions from other comments from @hugoberry and @ImkeF but I don't think they work for this situation.
Data:
{ "CreditMemo": { "RemainingCredit": 0, "domain": "QBO", "sparse": false, "Id": "73", "SyncToken": "3", "CustomField": [{ "DefinitionId": "1", "Name": "Crew #", "Type": "StringType", "Value": "12345" }, { "DefinitionId": "2", "Name": "second#", "Type": "StringType", "Value": "xyz123" }], "DocNumber": "1026", "TxnDate": "2014-09-02", } }
Using the UI it is very easy to expand out the lists and records
let Source = Json.Document(File.Contents("C:\Users\Desktop\example_credit2.json")), #"Converted to Table1" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table1", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}), #"Expanded CustomField" = Table.ExpandListColumn(#"Expanded Value", "CustomField"), #"Expanded CustomField1" = Table.ExpandRecordColumn(#"Expanded CustomField", "CustomField", {"DefinitionId", "Name", "Type", "Value"}, {"DefinitionId", "Name.1", "Type", "Value"}) in #"Expanded CustomField1"
It gets me this
Ideally I want to
Solved! Go to Solution.
Yes, a shorter way is this:
let Source = Json.Document(File.Contents("C:\Users\Desktop\example_credit2.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}), ToTable = Table.AddColumn(#"Expanded Value", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.FromRecords([CustomField]), {"Name", "Value"})))), #"Expanded Custom" = Table.ExpandTableColumn(ToTable, "Custom", Table.ColumnNames(ToTable[Custom]{0})) in #"Expanded Custom"
It is also dynamic: If there will be more "columns" in the CustomField, they will be expanded automatically as well.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Yes, a shorter way is this:
let Source = Json.Document(File.Contents("C:\Users\Desktop\example_credit2.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}), ToTable = Table.AddColumn(#"Expanded Value", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.FromRecords([CustomField]), {"Name", "Value"})))), #"Expanded Custom" = Table.ExpandTableColumn(ToTable, "Custom", Table.ColumnNames(ToTable[Custom]{0})) in #"Expanded Custom"
It is also dynamic: If there will be more "columns" in the CustomField, they will be expanded automatically as well.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
This works great except there are times when a nth column is listed but doesn't have a value (i.e. null). I receive this error. Any thoughts on how to adjust the Table.ExpandTableColumn function to prevent it from removing the record?
Expression.Error: The field 'Value' of the record wasn't found.
Details:
DefinitionId=2
Name=Other
Type=StringType
Here is what the JSON could look like. The second nested record has Id, Name, Type, but no Value. What do you think @MarcelBeug?
{ "CreditMemo": { "RemainingCredit": 0, "domain": "QBO", "sparse": false, "Id": "73", "SyncToken": "3", "CustomField": [{ "DefinitionId": "1", "Name": "Crew #", "Type": "StringType", "Value": "12345" }, { "DefinitionId": "2", "Name": "second#", "Type": "StringType" }], "DocNumber": "1026", "TxnDate": "2014-09-02", } }
My first thought is that is not very polite to @ImkeF if she solves your issue, and next you address someone else (i.c. me) for follow up.
Anyhow, I added a step "Completed Records" to Imke's code, so the fields "Name" and "Value" will always be present.
I'm pretty sure Imke would have come up with something similar.
let Source = Json.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\example_credit2.json")), #"Converted to Table" = Record.ToTable(Source), #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}, {"RemainingCredit", "domain", "sparse", "Id", "SyncToken", "CustomField", "DocNumber", "TxnDate"}), #"Completed Records" = Table.TransformColumns(#"Expanded Value",{{"CustomField", each List.Transform(_, each Record.SelectFields(_,{"Name","Value"}, MissingField.UseNull))}}), ToTable = Table.AddColumn(#"Completed Records", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(Table.FromRecords([CustomField]), {"Name", "Value"})))), #"Expanded Custom" = Table.ExpandTableColumn(ToTable, "Custom", Table.ColumnNames(ToTable[Custom]{0})) in #"Expanded Custom"
Hi @MarcelBeug: I don't consider it as impolite to suggest solutions anywhere. (But that might actually be a minority standpoint 😉 )
I cannot state often enough how much I value your solutions: using "MissingField.UseNull" is new to me, so thanks a lot for stepping in here!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hey Marcel. Didn't want to hurt anyone's feelings. I've learned a lot in 24 hours reading hundreds of help topics. I saw some similar posts you commented on and felt that this was very similar (hence the @) @ImkeF I figured you deserved a break for the evening. Thanks again to both of you and your dedication to this forum. Its helping me become the ETL expert I want to be.
Hopefully I can rise to your level some day!!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |