Mike Allen's Blog

Doing Smarter Things With IT

Power Query Primes in Excel

I’m going to show you how to calculate primes in Excel using Power Query and the Sieve of Eratosthenes. This is Power Query without tables, and highlights how to use parameters, functions, loops and recursions in Power Query, all fun stuff; and to make it even more fun we’re doing it in Excel. Download the spreadsheet to have a play Power Query Primes.

Open the spreadsheet and select Data>Queries & Connections. You will see the interesting stuff on the right, look at PrimeLimit, which you can edit this parameter which is the number up to which we will calculate primes. Then have a look at fxSieveGenerate, this is a function that gives us a list of numbers for the sieve as defined in the algorithm using List.Generate

Here’s the code for fxSieveGenerate:

     Source = (pStart as number, pLimit as number) as list =>
            xList = List.Generate(() => pStart * 2 , each _ < pLimit, each _ + pStart)

It’s a very simple function with two parameters, the start number and the limit. Now we are ready for the main act, the PrimeList query. This uses some useful PowerQuery techniques, looping is the main feature, of course:

     //PrimeLimit = 1000,
     SieveLimit = Number.Sqrt(PrimeLimit),
     Source = fxSieveGenerate(1, PrimeLimit),
     SieveNumbers = (parameter as record) as record =>
          Iterator = parameter[pIterator] + 1,
          Sieve = fxSieveGenerate(parameter[pPrimes]{Iterator},PrimeLimit),
          NewPrimes = List.RemoveMatchingItems(parameter[pPrimes],Sieve),
          AllPrimes =
          if NewPrimes{Iterator+1} <= SieveLimit then

     PrimeList = SieveNumbers([pPrimes=Source,pIterator=-1])

First up note the comment using //, always handy I used it here because PrimeLimit started as a variable, then I made it a parameter and commented the line out. Then I generate the starting number set, so I pass one(1) and the function doubles it and increments it by 1 for our starter list. I called this Source, which is kind of traditional Power Query naming convention. Next, we declare a function called SieveNumbers, which is highlighted. It’s worth pointing out that lists are accessed by the iterator and zero(0) is the first element. The function has a record as a parameter and returns a record when it is finished. The record is not clearly visible, but we give it two fields the list and the iterator. The function takes the list generates a new list using fxSieveGenerate, uses List.RemoveMatchingItems to remove those numbers and then recursively calls itself using @SieveNumbers until it reaches the sieve limit. The real work is then done by calling the function with a record to start it off, note the –1 in the iterator so that it starts at 0 in the function:
PrimeList = SieveNumbers([pPrimes=Source,pIterator=-1])

The list is then returned to the Excel sheet.

Note that this is Power Query in Excel and no tables were used or harmed in this exercise. I wrote this as an experiment on recursion and looping in Power Query/ Any feedback greatly appreciated, find me on Twitter




LinkedIn Advertising - Parsing the Data

I recently did some advertising on LinkedIn and wanted to analyse the demographic that I was hitting. The Campaign Manager has an export button, so I used this to download a Demographics CSV. The data was typical CSV, what some would refer to as unstructured. This analyses the data into eight segments, there are eight segment tables in the CSV data, my first thoughts were how to split and combine these segments into one structured dataset for further analysis in PowerBI.

Let’s have a look at the table:

That’s the Segment that I have highlighted, the data underneath the segment we’ll call ‘Category’. Note the word ‘Segment’, which we will come back to and use to identify this row of data.

The tables are separated by a blank row, followed by the next segment header:

These are the segments that we can extract. There’s an empty ‘County’ Segment as well, that gets ignored because it’s empty.

Let’s talk Power Query, here’s the finished query. I know this is what you came here for:

     Source = Csv.Document(File.Contents("C:\Users\mike\Downloads\demographics_report.csv"),[Delimiter="    ",
                                                     Columns=9, Encoding=1200, QuoteStyle=QuoteStyle.None]),
     #"Removed Top Rows" = Table.Skip(Source,5),
     #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column3", "Column5", "Column6",
                                                                 "Column7", "Column8", "Column9"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Category"}
                                                             ,{"Column2", "Impressions"},
                                                                  {"Column4", "Clicks"}}),
     #"Added Segment" = Table.AddColumn(#"Renamed Columns", "Segment",
                                     each if Text.Contains([Category],"Segment") then [Category] else null),
     #"Filled Down" = Table.FillDown(#"Added Segment",{"Segment"}),
     ReplaceWord = Table.ReplaceValue(#"Filled Down"," Segment","",Replacer.ReplaceText,{"Segment"}),
     FilterHeaders = Table.SelectRows(ReplaceWord, each not Text.Contains([Category], "Segment")),
     FilterTrailers = Table.SelectRows(FilterHeaders, each ([Category] <> ""))

This is fairly routine and was all done from the Ribbon, at the  #"Added Segment" step, I use Add Column –>Custom Column, typing in this formula:

if Text.Contains([Category],"Segment") then [Category] else null)

That null is very important for the next step, our data now looks like this:

That last column is our segment column, for the next step we select the Segment Column and go to the ribbon for Transform->Fill, which gives us:

The remaining steps, remove the word ‘Segment’ from the segment column and get rid of headers and trailers, so that our final product is like this:

This basically works, I will need to refine the Add Column so check a little more carefully to make sure it’s got a header. The code above is working code that you can cut and paste into your own solution. Feedback and comments are always welcome, you can do that on Twitter or LinkedIn. 


EMA Calculations with Power Query

This is my first attempt attempt at EMA calculation in Power Query, this is a work in progress. My intention is to use this work as a basis for technical indicators used in the stock market such as Bollinger Bands.

I have taken my EMA Example from https://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:moving_averages which I find very useful on the subject. I was also informed by Reza Rad at https://radacad.com/list-accumulate-hidden-gem-of-power-query-list-functions-in-power-bi which showed me how to use List.Accumulate, then I just needed to find an application for that knowledge and here it is.

The data that I have used is from the stockcharts.com EMA example, this helped me validate my solution, I started from their downloadable data. I’ve put some comments in the code, the magic is in the List.Accumulate, of course. The input data is in an Excel Workbook, in a table called “DataTable”, with two columns “Date” and “Close”

     Source = Excel.Workbook(File.Contents("emadata.xlsx"), null, true),
     DataTable_Table = Source{[Item="DataTable",Kind="Table"]}[Data],
     #"Changed Type1" = Table.TransformColumnTypes(DataTable_Table,{{"Date", type date}, {"Close", type number}}),
     EMA_Factor = .1818 ,
     Average = 10 ,
     #"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Close", type number}}),
     // Add an Index to out close price table
     #"Add Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
     //  Create a list of the closing prices
     ClosePriceList = #"Add Index"[Close],
     // Calculate SMA , the Simple Moving Average
     #"Add SMA" = Table.AddColumn(#"Add Index", "SMA", each if [Index] >= Average then List.Sum(List.Range(ClosePriceList,[Index]-Average,Average))/Average else ""),
     // Get the first SMA value to be the first EMA
     StartEMA = Table.Range(#"Add SMA",Average-1,1)[SMA]{0},
     // Calculate the Expotential Moving Average
     #"Add EMA" = Table.AddColumn(#"Add SMA", "EMA", each if [Index] >= Average then List.Accumulate(List.Range(ClosePriceList,Average,[Index]-Average),StartEMA,(state,current)=>((current - state) * EMA_Factor) + state) else "")
     #"Add EMA"

Here’s a picture of the final output, any feedback is greatly appreciated, thank you for reading:


Aging Debt using Power Query Lists

Power BI problems that require accessing previous rows of data can be difficult in Power BI, one way of resolving this is to translate columns into lists and then process the lists. This is an example that performs debtors aging, where we have one payments and five aging buckets that we wish to allocate those payments into. Please refer to my previous post http://mikeallen.com.au/post/Creating-an-Aging-Calendar-in-Power-Query as to how the aging buckets are created and assume that transactions have been grouped similar buckets numbered B1 thru B5.

Here is some sample data:


Here are the payments:


The method I’m going to use to age involves comparing the payment to the cumulative debt on the account going from B5, the oldest to B1 the newest,  so I need to get the data to look like this:


When I have this data I then apply this formula to each line to calculate the aging value:

if [Payment] >= [Cumulative] then 0
                     else if [Payment] < [Cumulative] and [Payment] > ([Cumulative] - [Actual])
                             then [Cumulative] - [Payment] else [Actual])

This handles the three cases of a fully paid, partially paid and unpaid buckets in one calculation, which I think is pretty neat!

Here is the Power Query that does all this, this is a Beta, I’m still refining it. The data is held in an Excel workbook in the format as shown above:

     Source = Excel.Workbook(File.Contents("BucketAging.xlsx"), null, true),
     Buckets_Sheet = Source{[Item="Buckets",Kind="Sheet"]}[Data],
     #"Promoted Headers" = Table.PromoteHeaders(Buckets_Sheet, [PromoteAllScalars=true]),
     #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Account", "Type", "Value"}),
     GroupRowsByAccount = Table.Group(#"Removed Other Columns", {"Account"}, {{"DataTable", each _, type table [Account=number, Type=text, Value=number]}}),
     MergePayments = Table.NestedJoin(GroupRowsByAccount, {"Account"}, Payments, {"Account"}, "Payments", JoinKind.LeftOuter),
     ExpandPaymentValue = Table.ExpandTableColumn(MergePayments, "Payments", {"Value"}, {"Value"}),
     RenamedPaymentColumn = Table.RenameColumns(ExpandPaymentValue,{{"Value", "Payment"}}),
     AddBucketNumbers = Table.AddColumn(RenamedPaymentColumn, "Bucket", each List.Numbers(1,5)),
     ExpandBuckets = Table.ExpandListColumn(AddBucketNumbers, "Bucket"),
     AddAgingList = Table.AddColumn(ExpandBuckets, "AgingList", each [DataTable][Value]),
     AddCumulativeValue = Table.AddColumn(AddAgingList, "Cumulative", each List.Sum(List.FirstN([AgingList],[Bucket]))),
     AddActualValue = Table.AddColumn(AddCumulativeValue, "Actual", each [AgingList]{[Bucket]-1}),
     AddAgingCalc = Table.AddColumn(AddActualValue, "Aging", each if [Payment] >= [Cumulative] then 0
                     else if [Payment] < [Cumulative] and [Payment] > ([Cumulative] - [Actual])
                             then [Cumulative] - [Payment] else [Actual]),
     AddBucketList = Table.AddColumn(AddAgingCalc, "AgingBuckets", each [DataTable][Type]),
     AddAgingBucket = Table.AddColumn(AddBucketList, "AgingBucket", each [AgingBuckets]{[Bucket]-1})

That’s all expanded out, so remove the columns that you don’t want.

I’ve tried to make the code self-documenting, it is conceptually fairly simple, you group the data down to a row for each account, expand it out for each bucket (in reverse order), then convert the values into a list and perform arithmetic on the list values while extracting the values back out again. Have a play, Lists in Power Query make it possible to do computation across rows of data.

This technique has been tested on several thousand accounts and performs well, any feedback is appreciated, this information is provided with no guarantee whatsoever Smile.


Creating an Aging Calendar in Power Query

Getting the best out of your data in PowerBI often requires the creation of specialized calendars that help you analyze your data the that you need it. I’ve been creating calendars using Power Query. Another problem I’ve had is where to store simple reference data, like the aging buckets for an Aging Calendar. So I combined these two issues and this is my solution. I must emphasize that this is not the only one way of solving this problem and may not suit your requirements, but take my thoughts and see if you find them useful.

Here is the Power Query:

     AgingTable = #table(

     EndDate = Table.AddColumn(AgingTable, "EndDate", each Date.AddDays(DateTime.Date(DateTime.LocalNow()),-[start])),
     StartDate = Table.AddColumn(EndDate, "StartDate", each Date.AddDays(DateTime.Date(DateTime.LocalNow()), -[end])),
     Duration = Table.AddColumn(StartDate, "Duration", each Duration.Days([EndDate]-[StartDate])+1),
     Datelist = Table.AddColumn(Duration, "DateList", each List.Dates([StartDate],[Duration],#duration(1,0,0,0))),
     #"Expanded DateList" = Table.ExpandListColumn(Datelist, "DateList"),
     #"Removed Columns" = Table.RemoveColumns(#"Expanded DateList",{"start", "end", "EndDate", "StartDate", "Duration"}),
     #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DateList", "Date"}})
     #"Renamed Columns"

This is fairly simple, but put together creates, I think, an elegant solution for an aging calendar. There’s an order column in the AgingTable, so that you can get your buckets in the right sequence on reports. I’ve started the date from today, but you can use other options, such a parameters. You can also insert this query into a PowerBI service DataFlow, which I’m starting to favor for my PowerBI calendars. Another nice technique here is the use of the list to generate the dates. Lists are one of the most powerful features of Power Query.

Let me know what you think, all feedback is greatly appreciated. Oh, and this advice is offered without any guarantee Smile

Information Ownership and Responsibility

All the information in a business system has an owner, what is interesting is that the owners of information may be unaware of its existence and their responsibilities as an owner. A computer system is a model of a business system, not a perfect model but a useful model. This is a generalization that applies to many types of model. Ownership and responsibility are implemented via business rules, which may be implemented in both code and data.

The owner of information may be the creator, the maintainer of even the reader of information. You will not find ownership by looking at the data or even at the code, it is intrinsic to a business system. The owner is responsible for the processes that affect information. What is even more interesting is that ownership is contextual and can vary over time.

Let’s dig a little deeper into the concept of ownership. An owner may be a person but is better described as an actor. An actor may be an entity like the accounts department, so that ultimate responsibility may lie with the manager of that department however ownership will be implemented by proxies such as accounts clerks and computer systems. As a piece of information travels through the business system , context changes, and ownership may change. For instance, as a customer transforms from a lead to an opportunity and then becomes a customer. So a piece of information can have multiple owners, but a sensible business rule would be for an instance of information to only have one ultimate owner for any given context. These actors should exist in a hierarchy with the ultimate owner delegating down the hierarchy of contextual actors and their proxies.

Why is this important? Well, information ownership is implicit in any business system and in order for a business system to function in a predictable fashion you need to know who is responsible for your data. Let's get back to customers, the core of any business. A business system is a model itself that will consist of several computer systems at various levels of integration. The problem is to identify a customer uniquely across all the systems that they are with. Duplicate (or more) customer records are the bane of any organization and ownership of customer identity is essential for managing customers. Duplicate records should be the exception, not the rule.

An example is often found in payment systems, businesses have multiple methods of making payments which often leads to multiple methods of generating account number. An account number and associated customer records should be raised by the information owner or their proxy. For those that would say this is impractical, I would say that this is what we have computer systems for. My favourite complaint is the use of cut and paste in business environments. Successfully implemented ownership will eliminate this.

Identification is the key to managing information ownership. Information ownership is the key to quality data. Quality data is key to high functioning computer systems and the resultant data analysis for decision management using BI and AI tools.

Analysis, design and improvement of business systems and the computer systems rely on establishing lean efficient information management. But let’s move on to the ownership of transactional data. Transactions frequently flow across multiple computer systems and multiple owners. An order is raised from a website entered into payment systems, general ledgers and order systems. At the end of the day we must be able to reassemble this information into unique transaction in order analyze sales performance.

This is a complex area that can yield high returns in the areas of business efficiency and planning, but again it requires identifying areas of responsibility. Then when dealing with business rules for tricky areas such as refunds and warranty, we will know which actors are involved.

When we know who owns the information we can also better identify the consumers of information, but I’ll leave that for another day.

Lean Information Governance

Good decision making is predicated on good information. A simple statement of the obvious. Who would want to run a business using poor quality decisions? This is all good in theory, what happens in practice is generally less than ideal. I’m going to treat that as a given.

When we proceed down the path of Business Intelligence(BI) and Artificial Intelligence(AI) we assume that our data is good enough and up to the task. Information is tricky stuff, sometimes we are not even aware of it until after the event that generated it has passed. Information also relies on the quality of the underlying data. Data can be defined succinctly, but information must be good enough for the task at hand.

Information governance is about ensuring that sources of information produce adequate and effective data of good quality. One of the principal issues of efficient data collection is knowing what data needs to be collected for an event or function that triggers the collection process. Suppose that you are recording the arrival of passengers at a train station, what data do you collect? Ticket number, destination, size, weight, how they arrived?

If the metrics of decision making are known the source data may be collected. Metrics are defined by combining source data which may be referential or transactional. Referential data is collected when the referential entity, such as a customer is identified for transactional purposes. Referential data, once collected must be maintained on a regular basis. Maintenance of referential data is one of the most important parts of data governance.

Maintaining referential data must be an ongoing task, some aspects can be usefully outsourced, such as credit ratings and corporate addresses. Personal details need to be kept up to date and meaningful, audit trails need to be kept. The concepts of master data need to be explored and responsibility and ownership of data need to be understood. This understanding needs to extend beyond business analysts and management. Data knowledge is an essential part of business culture, as relevant information is essential to the good operation of a business.

Transactional data is the lifeblood of any organization. Transactional data represents business activity and is the source of the fact tables that drive BI and AI. Transactional information is best collected as close to the source, in both time and location, as is possible. It is much harder to determine the essential data of a passenger at the destination than at the origin, purely because the start of a journey starts a transaction, whereas arrival at a destination implies a transaction in progress. Transactional data is inherently mutable, states and values change as the transaction is processed through the system. Process (or is that data?) analysis may indicate that we don’t need to record departures and arrivals as a single transaction. Then a change to the process, such a rewards system, makes it a necessity.

An excellent indicator of the utility of transactional data is how it is used. If it is used ubiquitously and is the one source of truth, it is very healthy data. If there are a variety of shadow systems that produce alternative versions of the truth, there will be issues with the data collections process. When the same information is collected multiple times (I’ve already been asked that a dozen times), that is a prime indicator of transactional torpor. In the same way, that reference data must have a master, transactional data must have a master and ownership. Transactional data often spawns subsidiary transactions in accounting ledgers and order books. These transactions must refer to the same reference data and this requirement must be handled in an appropriate fashion.

The key message here is that data is not separate to process, it is an intrinsic part of any process. Creators and consumers of business data need to have full awareness of what general and specific process rules exist in an organization. Another key concept is DRY (Don’t Repeat Yourself), collect information once. as efficiently as possible. Information Governance should be part of organizational culture. Executive responsibility for information governance must nurture that culture.

Being lean does not require a specific methodology, but lean information governance should be part of any continuous improvement effort. When you are faced with the latest flavour of business improvement, ask yourself if it incorporates better informational awareness into the process.

Analyzing The Edge (Look before you leap)

All computer systems have edges, edges in space and time. Computer Systems have attractive cores, so that they survive, but out at the edges they have less attractive features. This is part of the organic nature of systems they have a start and an end and they occupy space in a business, they also interweave and overlap the business process that they enable. In any system the edge is a key point of failure, the place where assumptions and assertions tend to fail.

Working the edges requires some good problem, cost and risk analysis skills. Think of this as stress, money and time factors. Ideally these problems are addressed at the leading edge, but it is not possible to address all these issues before they occur. We must address edge issues continuously, in an agile fashion; the edge is a place to look for potential issues.

The leading edge changes it’s profile over time along with the system lifecycle. Early edge events are important as they strongly influence system viability, that’s success or failure, but also core future areas like usability. Usability issues typically present as edge issues, such as interfaces to other systems, but in poorly designed systems will present as a core issue. Design is a leading edge issue, too little design leads to usability problems, over design will slow progress and often lead to rework. There is a sweet spot for leading edge issues, you may not know exactly where it is, but you can head towards it by making informed decisions.

The most common method of creating an edge issue is ignorance, mainly of the ‘it’s not important’ variety, making misguided assumptions because it is easier. This leads to assumptions that a problem is simple, therefore it requires no design thinking. Cost benefit and risk analysis are an important part of design and always should be considered.

A high  level design that can be justified by a few simple metrics, not only focuses thought, it aids collaboration. Analyzing the edge quantifies the area of interest with questions like how much, how many and how often? This leads to what does it cost and why do we do this? What are our usability profiles.

Interfaces with other systems are key edges. How is data synchronized, is there a master? Interfaces can fall into disrepair, then they are propped up my human intervention and this becomes a new workflow. People become accustomed to working around edge issues and may sometimes feel that that is how the system works. These issues can be hard to detect and address, but yield large benefits when they are fixed.

This is about getting more value from your software, data and technology, edge cases are a great place to start.

On Upgrading to a New Website

I have a business web site at Mike Allen Systems, I had already done some design and content and was ready to add some more business content and improve the graphics.

In the design process I had gone back to basics and used HTML and CSS, using W3-CSS to give me a simple responsive layout. That was all good: basic, simple and responsive, so I’d ticked all those boxes. One area of technology I’d fudged slightly was the layout, I’d used HTML server side includes for my headers, footers and menu. All this with minimal JavaScript. The problem I now had was how make the web site easily extendable in order provide more business functions.

I have to admit that the moment I started writing HTML and steering away from JavaScript, I had set my direction. I’d just set up a WordPress site for a customer, and while I respect WordPress, I wasn’t going to go there for my own needs. I have some programing skills in C#, so I looked at Razor Pages . This wasn’t a simple choice, I tried various aspects of creating just Razor Pages with a Layout replacing the includes, that was the simple bit. Then I got into creating an ASP .NET Core 2 application, which I discovered gave me what I needed, particularly for form pages.

Converting my pages to a Razor project was simple and tested nicely on the desktop. I used SendGrid on my contact form, that is nice and clean to use. What I hadn’t really considered was how to deploy this fairly new technology. My current web host didn’t support ASP .NET Core 2 on my server. Bummer! I regrouped and deployed to Azure and that was OK. I looked at Custom URL and SSL on Azure and found that put me in a higher price range. I looked for a more suitable host.

Finding a good host has always been one of the trials for web sites. Sometimes and excellent host will be taken over and service will decline, and the opposite can happen and a host can dramatically improve services. I’ve been using Crazy Domains for years for domain registration. Years ago their support for the .NET I needed was limited, but I gave them a call to see if they supported Core 2, and they did. I deployed and was up and running straight away.

This was a fairly simple technology upgrade for a web site, but it still consumed time and effort. My take away is to always consider deployment up front as part of the planning process, in order to avoid obstacles in the path of successful completion. It’s also important to be flexible, so I did write a little JavaScript to power my quote engine.

In summary, don’t forget to plan, but don’t expect activities to progress as planned, there are a lot of components involved in a web site upgrade, so allow time for discovery of issues and remediation. I also found the upgrade led me to consider other issues, such as how I manage images, keywords and content, that is for future projects.

Improved Xero Invoice extraction using Linx

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:

         "InvoiceType": "ACCPAY",
         "ContactName":"Melrose Parking",
         "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:

     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}})
     #"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.