Split a cell into multiple rows and calculating values

[JenaT]

I have invoice data (product names and purchased pieces aggregated on 1 row) that I need to transform into sales data, i.e. disaggregate the product name from the nr of pieces and also split the sales value between the products.

An illustration of the data and the required result is below:

My logic was that I can split the products from the purchase amounts by separator "-" into multiple columns, then somehow split them into rows, but how do make the sales value calculations (e.g. look at the latest purchase of the same product and take the value from there)?

There could be up to 5 products purchased in a single invoice that we need to split all into different rows.

[MFelix]

@JenaT,

Is your information with a line break a you present in you sample data?

If so you have to first split the cell by row by the line break simbol "#(lf)" and then make the split by columns by the "-".

Be aware that taking into account the sales columns you present the calculation of the Sales will bee repeat in all of your columns so the second row you present will have:

Jill | 13/07/2018 | Product 1 - 1 | 50

Jill | 13/07/2018 | Product 2 - 4 | 50

See below the screenshots and attach a PBIX file.

Regards,

MFelix

[JenaT]

Thanks, I did not know about the line feed.

But I am still presented with the problem of splitting sales amounts between the products. Is there any possible solution to that using the advanced editor for example or any other way?

[MFelix · Accepted solution]

Hi @JenaT,

The question is how you make that split is the unit price always the same for the same invoice?

You have several quantities but only one line for total value, what is the rule to get the split per product?

Regards,

MFelix