Xero data via Flatly: Group line item sales by category

[schnarf78]

We have restructured our chart of accounts & products in Xero (taken from Flatly as a direct link to Xero using Power BI is extremely limiting), and I want to start reporting on trends and profitability by types of products & services we sell.

I have a list of all GL accounts and a categorisation of each one, but the Invoices data from Xero makes this difficult to create a relationship.

The structure of Xero data is that you can have up to 77 line items on an invoice, and each line item has it's own data set:

What I want to do is say anything bought or sold to GL Account 4120 or 5120 forms part of Product A.  But I can only create one relation between GL Account and LineItems_?_AccountCode (where ? is from 1 to 77):

How do I create a relationship between GL Account and the numerous LineItems?

I cannot use product code for this summarisation, as historically generic codes were used instead of actual products, so the GL Account and my sub-categorisation as the only way I can see this working.  Unelss someone has a better idea?

[schnarf78]

Following on from this, I want to thank @DataInsights for the guidance and support.  It took a while to find time to really understand how to Unpivot, but eventually I did work it out.  While it was not easy selecting 30+ columns spread throughout the dataset, it did work!
However unbeknownst to me, Flatly created a version of Invoices (with a + sign in the title) that already did this for me for all aspects of the data.  I've since requested they do the same for Credits (which are completely separate from Invoices) and Journals, so now I get a better file format.

Thanks again for your help.

[DataInsights · Accepted solution]

@schnarf78,

You could unpivot the columns for each line number so your data is in a usable format. See the article below:

https://datachant.com/2019/10/11/guest-story-unpivot-pairs-of-columns/ 

The concept:

1. Merge each pair of columns (Product 1 and Quantity 1, Product 2 and Quantity 2, etc.) using a delimiter.
2. Unpivot the merged columns.
3. Split the Value column by the delimiter.

[schnarf78]

Hi @DataInsights - thank you for the suggestion.  I followed the guidance to merge columns (IE LineItems_1_AccountCode and LineItems_1_ItemCode), and then split the newly merged column, but that really doesn't solve the issue.  I still have 75 groupings to merge and re-split and still will have 75 iterations of AccountCode or item code (or the merged column if I didn't split it).

I tried the unpivot other columns suggestion, but it then hid many of the columns so I'm not sure I truly understood that.

Unless I'm missing something in the link you sent?  A lot of that was very advanced data manipulation so I may not have understood the instructions fully.

Chris

[DataInsights]

@schnarf78,

Would you be able to provide a link to a pbix with sample data, or post the data so I can copy/paste it into a pbix? Also, could you provide a mockup of the desired result?

[schnarf78]

Not sure if this link will work.  Couldn't figure out how to copy the pbix file here, and copying the data table would look messy due to the hundreds of columns:

https://app.powerbi.com/links/zMZadZK4-M?ctid=f7405f46-5146-4d88-8a3d-06d7bd2f9b9c&pbi_source=linkSh...

The first set of data is customer invoice and line item which is split into columns (account code, item code, amount, quantity).

The second set of data is the chart of accounts, with code, name and description.

My end goal is to take any GL/account that starts with IDT (for example) to then view all sales/costs over time for this product group (original data set includes supplier invoices).  This will give us further insight into the profitability of our different offerings, with further analysis as we dig deeper.

Here's a rought mock-up:

[DataInsights]

@schnarf78,

When I clicked the link I got a message stating that the file has been removed or I don't have access. Are you able to save the pbix to OneDrive, Dropbox, etc.? That's a common way of providing access to pbix files on this forum.

[schnarf78]

Thanks.  I thought the link might not work...try this?
Xero GL Test 

[DataInsights]

@schnarf78,

I was able to access the file--thanks. Is this the result you want? The sample below is for the first two line numbers.

Here's the M code (Power Query). You need to replicate the steps MergeLine01, MergeLine02 ... MergeLine36.

let
  Source = Original,
  RemoveColumns = Table.SelectColumns(
    Source,
    {
      "Status",
      "SubTotal",
      "Total",
      "TotalTax",
      "CurrencyCode",
      "CurrencyRate",
      "DateString",
      "DueDateString",
      "InvoiceNumber",
      "LineItems_1_AccountCode",
      "LineItems_1_ItemCode",
      "LineItems_1_LineAmount",
      "LineItems_1_Quantity",
      "LineItems_2_AccountCode",
      "LineItems_2_ItemCode",
      "LineItems_2_LineAmount",
      "LineItems_2_Quantity"
    }
  ),
  MergeLine01 = Table.CombineColumns(
    Table.TransformColumnTypes(
      RemoveColumns,
      {
        {"LineItems_1_AccountCode", type text},
        {"LineItems_1_LineAmount", type text},
        {"LineItems_1_Quantity", type text}
      },
      "en-US"
    ),
    {
      "LineItems_1_AccountCode",
      "LineItems_1_ItemCode",
      "LineItems_1_LineAmount",
      "LineItems_1_Quantity"
    },
    Combiner.CombineTextByDelimiter("~", QuoteStyle.None),
    "MergeLine1"
  ),
  MergeLine02 = Table.CombineColumns(
    Table.TransformColumnTypes(
      MergeLine01,
      {
        {"LineItems_2_AccountCode", type text},
        {"LineItems_2_LineAmount", type text},
        {"LineItems_2_Quantity", type text}
      },
      "en-US"
    ),
    {
      "LineItems_2_AccountCode",
      "LineItems_2_ItemCode",
      "LineItems_2_LineAmount",
      "LineItems_2_Quantity"
    },
    Combiner.CombineTextByDelimiter("~", QuoteStyle.None),
    "MergeLine2"
  ),
  UnpivotOtherColumns = Table.UnpivotOtherColumns(
    MergeLine02,
    {
      "Status",
      "SubTotal",
      "Total",
      "TotalTax",
      "CurrencyCode",
      "CurrencyRate",
      "DateString",
      "DueDateString",
      "InvoiceNumber"
    },
    "Attribute",
    "Value"
  ),
  RenameColumn = Table.RenameColumns(UnpivotOtherColumns, {{"Attribute", "Line"}}),
  ReplaceMergeLine = Table.ReplaceValue(
    RenameColumn,
    "MergeLine",
    "",
    Replacer.ReplaceText,
    {"Line"}
  ),
  SplitColumn = Table.SplitColumn(
    ReplaceMergeLine,
    "Value",
    Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv),
    {"Value.1", "Value.2", "Value.3", "Value.4"}
  ),
  RenameColumns = Table.RenameColumns(
    SplitColumn,
    {
      {"Value.1", "Account Code"},
      {"Value.2", "Item Code"},
      {"Value.3", "Line Amount"},
      {"Value.4", "Quantity"}
    }
  ),
  ChangeType = Table.TransformColumnTypes(
    RenameColumns,
    {{"Line Amount", type number}, {"Quantity", Int64.Type}}
  ),
  RemoveRowsWithNoAccount = Table.SelectRows(ChangeType, each ([Account Code] <> ""))
in
  RemoveRowsWithNoAccount