cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
schnarf78
Regular Visitor

Xero data via Flatly: Group line item sales by category

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:

schnarf78_0-1624369469113.png

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):

schnarf78_1-1624369593642.png

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?

 

7 REPLIES 7
DataInsights
Super User II
Super User II

@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.

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

@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?

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:

schnarf78_0-1624550287765.png

 

@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.

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

@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.

 

DataInsights_0-1624559154245.png

 

 

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

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors