cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gospodinovm Frequent Visitor
Frequent Visitor

Nested JSON - not able to expand List between values in column lineitems.v.v.f.v.

Hi and thank you for looking at my post,

 

After expanding all possible Lists and Records we end up on a step where we are not able to expand List between values in column lineitems.v.v.f.v.

 

let
   Source = GoogleBigQuery.Database(null),
   #"xero-245302" = Source{[Name="xero-245302"]}[Data],
   client_account_Schema = #"xero-245302"{[Name="client_account",Kind="Schema"]}[Data],
   invoices_Table = client_account_Schema{[Name="invoices",Kind="Table"]}[Data],
   #"Parsed JSON" = Table.TransformColumns(invoices_Table,{{"lineitems", Json.Document}}),
   #"Expanded lineitems" = Table.ExpandRecordColumn(#"Parsed JSON", "lineitems", {"v"}, {"lineitems.v"}),
   #"Expanded lineitems.v" = Table.ExpandListColumn(#"Expanded lineitems", "lineitems.v"),
   #"Expanded lineitems.v1" = Table.ExpandRecordColumn(#"Expanded lineitems.v", "lineitems.v", {"v"}, {"lineitems.v.v"}),
   #"Expanded lineitems.v.v" = Table.ExpandRecordColumn(#"Expanded lineitems.v1", "lineitems.v.v", {"f"}, {"lineitems.v.v.f"}),
   #"Expanded lineitems.v.v.f" = Table.ExpandListColumn(#"Expanded lineitems.v.v", "lineitems.v.v.f"),
   #"Expanded lineitems.v.v.f1" = Table.ExpandRecordColumn(#"Expanded lineitems.v.v.f", "lineitems.v.v.f", {"v"}, {"lineitems.v.v.f.v"})
in
   #"Expanded lineitems.v.v.f1"

You can access a sample file via this linkhere, https://drive.google.com/a/winwinnkeeper.net/file/d/10kuxJ4SXSr6FMAcPp6tr1TY63WCyK5bz/view?usp=shari...

 

I will really appreciate your advice and best practice working with Xero JSON data because I saw some of the contributors topics to explain how to work with JSON but I cannot standardise my approach and our company receives quite a lot of data through JSON files. Each software has its own unique nested way to deliver JSON data which is quite difficult to work with in Power BI or maybe this could be solved in our datawarehouse, we use GoogleBigQuery?

 

Thank you for your time in advance.

 

3 REPLIES 3
gospodinovm Frequent Visitor
Frequent Visitor

Re: Nested JSON - not able to expand List between values in column lineitems.v.v.f.v.

I've seen a few functions from other comments from @MarcelBeug@hugoberry and @ImkeF but I don't think they work for this situation. 

Community Support Team
Community Support Team

Re: Nested JSON - not able to expand List between values in column lineitems.v.v.f.v.

hi, @gospodinovm 

I couldn't download your sample pbix file, and what is the error?

Could you share some json sample data instead of pbix file.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
gospodinovm Frequent Visitor
Frequent Visitor

Re: Nested JSON - not able to expand List between values in column lineitems.v.v.f.v.

hi, @v-lili6-msft ,

 

I have updated the link and now you should be able to see the my latest update on the pbix file.

https://drive.google.com/a/winwinnkeeper.net/file/d/10o3NcdvAU1CCZRDJBqoNcWm294ZhY6SK/view?usp=shari...

 

My files are nested many times and I was wondering what is the best way to deal with the new data that will create more rows? 

This is how I have dealt with NESTED JSONS but my file is becoming very slow and sometimes I am experiencing error message "We reached the end of buffer" which I doubt is the most efficient way to deal with JSON files, I have 12 more tables like this one using the same way to deal with the JSON files. I have attached my code below:

 

 

let
    Source = GoogleBigQuery.Database(null),
    #"xero-245302" = Source{[Name="xero-245302"]}[Data],
    client_account_Schema = #"xero-245302"{[Name="client_account",Kind="Schema"]}[Data],
    invoices_Table = client_account_Schema{[Name="invoices",Kind="Table"]}[Data],
    #"Grouped Rows" = Table.Group(invoices_Table, {"_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "lineitems", "isdiscounted", "payments", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact", "currencyrate", "amountdue", "creditnotes"}, {{"Sort_Column", each List.Max([_sdc_received_at]), type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Sort_Column", Order.Descending}}),
    #"Buffer table" = Table.Buffer(#"Sorted Rows"),
    #"Removed Duplicates" = Table.Distinct(#"Buffer table", {"invoiceid"}),
    #"Parsed JSON" = Table.TransformColumns(#"Removed Duplicates",{{"lineitems", Json.Document}}),
    #"Expanded lineitems" = Table.ExpandRecordColumn(#"Parsed JSON", "lineitems", {"v"}, {"lineitems.v"}),
    #"Expanded lineitems.v" = Table.ExpandListColumn(#"Expanded lineitems", "lineitems.v"),
    #"Expanded lineitems.v1" = Table.ExpandRecordColumn(#"Expanded lineitems.v", "lineitems.v", {"v"}, {"lineitems.v.v"}),
    #"Expanded lineitems.v.v" = Table.ExpandRecordColumn(#"Expanded lineitems.v1", "lineitems.v.v", {"f"}, {"lineitems.v.v.f"}),
    #"Expanded lineitems.v.v.f" = Table.ExpandListColumn(#"Expanded lineitems.v.v", "lineitems.v.v.f"),
    #"Expanded lineitems.v.v.f1" = Table.ExpandRecordColumn(#"Expanded lineitems.v.v.f", "lineitems.v.v.f", {"v"}, {"lineitems.v.v.f.v"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded lineitems.v.v.f1", "Index", 0, 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 10), Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Modulo", each Number.Mod([Index], 10), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-GB")[Modulo]), "Modulo", "lineitems.v.v.f.v"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"0", "CA_code"}, {"1", "CA_key"}, {"3", "VAT?"}, {"4", "Unit_Price"}, {"5", "Amount_GBP"}, {"6", "Description"}, {"7", "Quantity"}, {"8", "Total_No_VAT?"}, {"9", "Item_Code?"}}),
    #"Expanded 2" = Table.ExpandListColumn(#"Renamed Columns", "2"),
    #"Expanded 1" = Table.ExpandRecordColumn(#"Expanded 2", "2", {"v"}, {"2.v"}),
    #"Expanded 2.v" = Table.ExpandRecordColumn(#"Expanded 1", "2.v", {"f"}, {"2.v.f"}),
    #"Expanded 2.v.f" = Table.ExpandListColumn(#"Expanded 2.v", "2.v.f"),
    #"Expanded 2.v.f1" = Table.ExpandRecordColumn(#"Expanded 2.v.f", "2.v.f", {"v"}, {"2.v.f.v"}),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded 2.v.f1", "Index", 0, 1),
    #"Sorted Rows1" = Table.Sort(#"Added Index1",{{"CA_code", Order.Descending}, {"CA_key", Order.Descending}}),
    #"Inserted Integer-Division1" = Table.AddColumn(#"Sorted Rows1", "Integer-Division.1", each Number.IntegerDivide([Index], 3), Int64.Type),
    #"Group Row" = Table.Group(#"Expanded 2.v.f1", {"CA_key"}, {{"INV_countdistinct", each Table.RowCount(_), type number}, {"INV_allrows", each _, type table [_sdc_received_at=datetime, invoiceid=text, updateddateutc=datetime, _sdc_sequence=number, _sdc_batched_at=datetime, _sdc_table_version=number, total=number, currencycode=text, duedatestring=datetime, amountpaid=number, totaltax=number, hasattachments=logical, haserrors=logical, status=text, invoicenumber=text, isdiscounted=logical, payments=text, reference=text, type=text, subtotal=number, duedate=datetime, date=datetime, fullypaidondate=datetime, amountcredited=number, datestring=datetime, lineamounttypes=text, contact=text, currencyrate=number, amountdue=number, creditnotes=text, Sort_Column=datetime, #"Integer-Division"=number, CA_code=none, CA_key=none, 2.v.f.v=none, #"VAT?"=none, Unit_Price=none, Amount_GBP=none, Description=none, Quantity=none, #"Total_No_VAT?"=none, #"Item_Code?"=none]}}),
    #"Filtered Rows" = Table.SelectRows(#"Group Row", each ([CA_key] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Partitioned", each Table.AddIndexColumn([INV_allrows], "Index", 1, 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"INV_allrows"}),
    #"Expanded Partitioned" = Table.ExpandTableColumn(#"Removed Columns1", "Partitioned", {"_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "isdiscounted", "payments", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact", "currencyrate", "amountdue", "creditnotes", "Sort_Column", "Integer-Division", "CA_code", "CA_key", "2.v.f.v", "VAT?", "Unit_Price", "Amount_GBP", "Description", "Quantity", "Total_No_VAT?", "Item_Code?", "Index"}, {"_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "isdiscounted", "payments", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact", "currencyrate", "amountdue", "creditnotes", "Sort_Column", "Integer-Division", "CA_code", "CA_key.1", "2.v.f.v", "VAT?", "Unit_Price", "Amount_GBP", "Description", "Quantity", "Total_No_VAT?", "Item_Code?", "Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Partitioned", "Support", each if [Index] <= 6 then [Index] else 0),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "INV_countdistinct", "Integer-Division", "Sort_Column"}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns2", {{"Support", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns2", {{"Support", type text}}, "en-GB")[Support]), "Support", "2.v.f.v"),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"1", "Properties"}, {"2", "Properties_account"}, {"3", "Properties_key"}, {"4", "Costcenter"}, {"5", "Costcenter_account"}, {"6", "Costcenter_key"}}),
    #"Parsed JSON1" = Table.TransformColumns(#"Renamed Columns1",{{"payments", Json.Document}}),
    #"Expanded payments" = Table.ExpandRecordColumn(#"Parsed JSON1", "payments", {"v"}, {"payments.v"}),
    #"Expanded payments.v" = Table.ExpandListColumn(#"Expanded payments", "payments.v"),
    #"Sorted Rows2" = Table.Sort(#"Expanded payments.v",{{"invoiceid", Order.Descending}}),
    #"Expanded payments.v1" = Table.ExpandRecordColumn(#"Sorted Rows2", "payments.v", {"v"}, {"payments.v.v"}),
    #"Expanded payments.v.v" = Table.ExpandRecordColumn(#"Expanded payments.v1", "payments.v.v", {"f"}, {"payments.v.v.f"}),
    #"Expanded payments.v.v.f" = Table.ExpandListColumn(#"Expanded payments.v.v", "payments.v.v.f"),
    #"Expanded payments.v.v.f1" = Table.ExpandRecordColumn(#"Expanded payments.v.v.f", "payments.v.v.f", {"v"}, {"payments.v.v.f.v"}),
    #"Grouped Rows1" = Table.Group(#"Expanded payments.v.v.f1", {"CA_key"}, {{"Table", each _, type table [CA_key=text, _sdc_received_at=datetime, invoiceid=text, updateddateutc=datetime, _sdc_sequence=number, _sdc_batched_at=datetime, _sdc_table_version=number, total=number, currencycode=text, duedatestring=datetime, amountpaid=number, totaltax=number, hasattachments=logical, haserrors=logical, status=text, invoicenumber=text, isdiscounted=logical, payments.v.v.f.v=text, reference=text, type=text, subtotal=number, duedate=datetime, date=datetime, fullypaidondate=datetime, amountcredited=number, datestring=datetime, lineamounttypes=text, contact=text, currencyrate=number, amountdue=number, creditnotes=text, CA_code=text, CA_key.1=text, #"VAT?"=text, Unit_Price=text, Amount_GBP=text, Description=text, Quantity=text, #"Total_No_VAT?"=text, #"Item_Code?"=none, Properties=text, Properties_account=text, Properties_key=text, Costcenter=text, Costcenter_account=text, Costcenter_key=text, null=none]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Partition", each Table.AddIndexColumn([Table], "Index", 1, 1)),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom1",{"Table"}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Removed Columns3", "Partition", {"CA_key", "_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "isdiscounted", "payments.v.v.f.v", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact", "currencyrate", "amountdue", "creditnotes", "CA_code", "CA_key.1", "VAT?", "Unit_Price", "Amount_GBP", "Description", "Quantity", "Total_No_VAT?", "Item_Code?", "Properties", "Properties_account", "Properties_key", "Costcenter", "Costcenter_account", "Costcenter_key", "null", "Index"}, {"CA_key.2", "_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "isdiscounted", "payments.v.v.f.v", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact", "currencyrate", "amountdue", "creditnotes", "CA_code", "CA_key.1", "VAT?", "Unit_Price", "Amount_GBP", "Description", "Quantity", "Total_No_VAT?", "Item_Code?", "Properties", "Properties_account", "Properties_key", "Costcenter", "Costcenter_account", "Costcenter_key", "null", "Index"}),
    #"Pivoted Column2" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-GB")[Index]), "Index", "payments.v.v.f.v"),
    #"Renamed Columns2" = Table.RenameColumns(#"Pivoted Column2",{{"1", "PYMT_1"}, {"2", "PYMT_2"}, {"3", "PYMT_3"}, {"4", "PYMT_4"}, {"5", "PYMT_5"}, {"6", "PYMT_6"}, {"7", "PYMT_7"}, {"8", "PYMT_8"}, {"9", "PYMT_9"}, {"10", "PYMT_10"}, {"11", "PYMT_11"}, {"12", "PYMT_12"}, {"13", "PYMT_13"}, {"14", "PYMT_14"}, {"15", "PYMT_15"}, {"16", "PYMT_16"}}),
    #"Parsed JSON2" = Table.TransformColumns(#"Renamed Columns2",{{"contact", Json.Document}}),
    #"Expanded contact" = Table.ExpandRecordColumn(#"Parsed JSON2", "contact", {"v"}, {"contact.v"}),
    #"Expanded contact.v" = Table.ExpandRecordColumn(#"Expanded contact", "contact.v", {"f"}, {"contact.v.f"}),
    #"Expanded contact.v.f" = Table.ExpandListColumn(#"Expanded contact.v", "contact.v.f"),
    #"Expanded contact.v.f1" = Table.ExpandRecordColumn(#"Expanded contact.v.f", "contact.v.f", {"v"}, {"contact.v.f.v"}),
    #"Grouped Rows2" = Table.Group(#"Expanded contact.v.f1", {"CA_key"}, {{"Expand", each _, type table [CA_key=text, CA_key.2=text, _sdc_received_at=datetime, invoiceid=text, updateddateutc=datetime, _sdc_sequence=number, _sdc_batched_at=datetime, _sdc_table_version=number, total=number, currencycode=text, duedatestring=datetime, amountpaid=number, totaltax=number, hasattachments=logical, haserrors=logical, status=text, invoicenumber=text, isdiscounted=logical, reference=text, type=text, subtotal=number, duedate=datetime, date=datetime, fullypaidondate=datetime, amountcredited=number, datestring=datetime, lineamounttypes=text, contact.v.f.v=text, currencyrate=number, amountdue=number, creditnotes=text, CA_code=text, CA_key.1=text, #"VAT?"=text, Unit_Price=text, Amount_GBP=text, Description=text, Quantity=text, #"Total_No_VAT?"=text, #"Item_Code?"=none, Properties=text, Properties_account=text, Properties_key=text, Costcenter=text, Costcenter_account=text, Costcenter_key=text, null=none, PYMT_1=text, PYMT_2=text, PYMT_3=text, PYMT_4=text, PYMT_5=text, PYMT_6=text, PYMT_7=text, PYMT_8=text, PYMT_9=none, PYMT_10=none, PYMT_11=none, PYMT_12=none, PYMT_13=none, PYMT_14=none, PYMT_15=none, PYMT_16=none]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each Table.AddIndexColumn([Expand], "Index", 1, 1)),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom2",{"Expand"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns4", "Custom", {"CA_key", "CA_key.2", "_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "isdiscounted", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact.v.f.v", "currencyrate", "amountdue", "creditnotes", "CA_code", "CA_key.1", "VAT?", "Unit_Price", "Amount_GBP", "Description", "Quantity", "Total_No_VAT?", "Item_Code?", "Properties", "Properties_account", "Properties_key", "Costcenter", "Costcenter_account", "Costcenter_key", "null", "PYMT_1", "PYMT_2", "PYMT_3", "PYMT_4", "PYMT_5", "PYMT_6", "PYMT_7", "PYMT_8", "PYMT_9", "PYMT_10", "PYMT_11", "PYMT_12", "PYMT_13", "PYMT_14", "PYMT_15", "PYMT_16", "Index"}, {"CA_key.3", "CA_key.2", "_sdc_received_at", "invoiceid", "updateddateutc", "_sdc_sequence", "_sdc_batched_at", "_sdc_table_version", "total", "currencycode", "duedatestring", "amountpaid", "totaltax", "hasattachments", "haserrors", "status", "invoicenumber", "isdiscounted", "reference", "type", "subtotal", "duedate", "date", "fullypaidondate", "amountcredited", "datestring", "lineamounttypes", "contact.v.f.v", "currencyrate", "amountdue", "creditnotes", "CA_code", "CA_key.1", "VAT?", "Unit_Price", "Amount_GBP", "Description", "Quantity", "Total_No_VAT?", "Item_Code?", "Properties", "Properties_account", "Properties_key", "Costcenter", "Costcenter_account", "Costcenter_key", "null", "PYMT_1", "PYMT_2", "PYMT_3", "PYMT_4", "PYMT_5", "PYMT_6", "PYMT_7", "PYMT_8", "PYMT_9", "PYMT_10", "PYMT_11", "PYMT_12", "PYMT_13", "PYMT_14", "PYMT_15", "PYMT_16", "Index"}),
    #"Pivoted Column3" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-GB")[Index]), "Index", "contact.v.f.v"),
    #"Renamed Columns3" = Table.RenameColumns(#"Pivoted Column3",{{"1", "Contact_Name"}, {"2", "Contact_Key"}, {"3", "Contact_Status"}})
in
    #"Renamed Columns3"

 

Thank you very much for your help

 

Miro

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 43 members 1,011 guests
Please welcome our newest community members: