Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
schnarf78
Frequent 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?

 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
schnarf78
Frequent Visitor

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
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.