Forum Discussion

PortellaDM's avatar
PortellaDM
Frequent Visitor
4 years ago

split a column based on the attributes

Hi all, very new to PowerBI so please bear with me, I might not have all the correct lingo. 

I have data from Business Central, in which one column contains both invoice numbers and payment numbers. These are tied together with a Customer Ledger Entry number, that will be the same for both the invoice and its allocated payment. 

 

I need to create a report (table) that shows the invoice number, with its corresponding payment number next to it in the row. As these are both currently contained in the same column, but need to show separately in the report, I think I need to create filtered measures, one containing only the invoice numbers for a particular customer ledger entry, and one showing only the payment numbers for the same customer ledger entry. 

 

Should this be done in Power Query or in PowerBI itself? Measure or Calculated Column? Which formulas to use?

I have tried creating measures using FILTER, SWITCH TRUE, and LOOKUPVALUE but cannot get any results with any of these. 

 

As I said, quite a beginner so no doubt I am overlooking something fundamental.

 

Any help you could give would be greatly appreciated.

 

Need Help

2 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    You can split your column in the Power Query Editor. Can you share an example of what your current column looks like? You can split it a number of ways we just need to figure out what works for you!
    For example in my screenshot I made 1 column with 3 numbers seperated by "-" I can split them by going to the "Home" tab then "Split Column"  by delimiter fill out the info

     

     

     

     

     

     

    • PortellaDM's avatar
      PortellaDM
      Frequent Visitor
      4 years ago

      Hi Syk, thank you for such a speedy response!

      Below I have attached a screenshot of what it looks like in Excel. Effectively, I think I need one measure (or calculated column) to be filtered on Entry Type of Initial Entry, and display the Document No. and another measure to be filtered on Entry Type of Application, again displaying the Document No. Then as Cust. Ledg. Entry No. is the same in both invoice and payment, I should be able to show them in a table visual with Invoice as one column and Payment as another.