Mike Allen's Blog

Having successfully extracted Xero invoices using Linx, I decided to drill deeper an improve my understanding and methods.

My aim is still to extract the invoices and load them into Power BI, but now I have the additional target of loading the invoices into CosmosDB and then loading the CosmosDB collection into PowerBI, and eventual Azure Analysis Services.

I improved my understanding of Invoice Type, realizing that the data was both Accounts Receivable and Payable.

Here’s a sample of the data:

{
         "id":"0032f627-3156-4d30-9b1c-4d3b994dc921",
         "InvoiceType": "ACCPAY",
         "InvoiceDate":"2017-09-11",
         "InvoiceNumber":"9871",
         "Status":"PAID",
         "Total":148.5,
         "SubTotal":135.0,
         "TotalTax":13.5,
         "TotalDiscount":0.0,
         "DueDate":"2017-09-20",
         "ContactId":"d6a384fb-f46f-41a3-8ac7-b7bc9e0b5efa",
         "ContactName":"Melrose Parking",
         "UpdatedDate":"2008-09-16T20:28:51.5+10:00",
         "Reference":"",
         "AmountDue":0.0,
         "AmountPaid":148.5,
         "Url":null,
         "Partition": "201709"
     }

Key takeaways:

1. The ‘id’ is critical and must be named that to map to the id in the Cosmosdb
2. That Url, while null in the example, is the url of the Invoice PDF
3. I’ve added a ‘Partition’ field for CosmosDB and Analysis services use.
4. I’ve trimmed the time off InvoiceDate and DueDate
5. All the JSON formatting is done in Linx

Here’s a pic of the Linx project:

That InvoiceItem is a type I’ve defined with the format for the JSON

My thinking is that as the invoice status changes I can update CosmosDB using the id, the CosmosDB API has an ‘Upsert’ functionality which will do this neatly. The intention being to extract Xero Invoices that have changed in the last day on a daily basis using Linx and then upsert them to the CosmosDB and then refresh the Power BI/ Analysis Services Model.

I’m using a very simple C# program to do the upsert, this is all dynamic, with very little code.

foreach(dynamic invoice in invoices)
            {             
                await this.client.UpsertDocumentAsync(UriFactory.CreateDocumentCollectionUri(databaseName, collectionName), invoice,options);
            }

This code doesn’t care what the invoice looks like, the CosmosDB has no schema, but I insert into a collection called ‘Invoices’ with a consistent layout.

Also it is still possible to load the JSON directly to PowerBI, the query is very simple:

let
     Source = Json.Document(File.Contents("C:\Users\Mike\Documents\LinxOutput\invoice.json")),
     #"InvoiceTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     #"Expanded Column1" = Table.ExpandRecordColumn(InvoiceTable, "Column1", {"InvoiceDate", "InvoiceNumber", "Total", "Status", "id", "SubTotal", "TotalTax", "TotalDiscount", "DueDate", "ContactId", "ContactName", "UpdatedDate", "Reference", "AmountDue", "AmountPaid", "InvoiceType", "Url"}, {"InvoiceDate", "InvoiceNumber", "Total", "Status", "id", "SubTotal", "TotalTax", "TotalDiscount", "DueDate", "ContactId", "ContactName", "UpdatedDate", "Reference", "AmountDue", "AmountPaid", "InvoiceType", "Url"}),
     #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"InvoiceDate", type date}, {"DueDate", type datetime}})
      in
     #"Changed Type"

If you’ve got this far, thank you for your interest, there’ll be more on Xero, Linx, CosmosDB and PowerBI in the near future.