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
