@navsal66

Don't understand. You highlighted one entry with multiple invoice numbers and one total value. Beside the table, you split the total into amounts by invoice. How would PQ/Excel know the individual invoice amounts if only the total is given?

Or do you mean that the individual invoice amounts are available in another table/list. If that is the case perhaps the query in the attached file helps you on your way.

Sorry for the confusion. I'm facing an issue with invoice data. I'm receiving mingled invoices with mingled values, and I need to split them row by row. I've managed to separate the invoices using Power Query, but I'm struggling to split the values. Could you please suggest a solution for splitting the values?

invoices --> GF34942,GF34944,GF34947,GF34948,GF34954

value     --> =6586.7+3482.74+5401+730.47+5341.29

@navsal66 

OK, understood. Then perhaps a very crude query does what you need. Most likely it can be optimized, but for now it works. The trick is to add a helper column containing the FORMULA text or what's in the Value column or just the value if it's a single amount. See if this works for you.

Hi Riny,

I've attempted this multiple times, but unfortunately, it's not working as expected. Could you please share the steps or method you used to achieve this? I'd greatly appreciate your guidance and insight into how to resolve this issue. Because of i am a new user for PQ

Thank you for your time and assistance.

@navsal66 OK, being new to PQ is somewhat of a challenge. All the steps are listed in the “Applied steps” list. Most are done by clicking in the User Interface. Some are manually ‘coded'

The end result is a table of single invoice numbers and matching single invoice amounts.

But I do recognise that this is not intuitive at all. You need some (slightly) advanced PQ knowledge.

You might find the following helpful.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
https://www.excelcampus.com/install-power-query/

- Follow this link for an introduction to Power Query functionality.
https://www.excelcampus.com/powerquery/power-query-overview/

- Follow this link for a video which demonstrates how to use Power Query code provided.
https://excel.solutions/2017/11/power-query-paste-code-video/

@riny 

Hi Riny,

well noted and thanks for your suggestion, i will try my best to learn PQ.

Hi Mr. Alan

Thanks for your support & Links i will try above links.