Convert data in row to columns

[paulo01]

Hi Everyone.

I am doing an analysis of sales. The download includes product, revenue, cost of sale and quantity sold for every transaction.

However, there are also "bundled" sales whereby Bundle A = product X and product Y and product Z.

I have set up the data, so when the bundle name is recognised, it returns product X (rev, cost, quantity) on the row, product Y (rev, cost, quantity) on the same row next to that etc.

In my pivot, I have a calculated field where I sum the relevant columns to find the totals. But if I filter by product, I can only choose one column which is assigning the whole rows totals to that one product.

I want to transform the data in a single row that has multiple products so that its listed individually in rows.

However, there is no set number of products in each bundle (can be anywhere from 1 to 12).

If anyone know of a solution it would be much appreciated.

Thanks

 

[mart37]

In Power Query you can combine each set of 4 columns to one column with a delimiter (
With unpivot columns you can make one column with all your data and a title column wich can be removed.
Split your column on the delimiter (
remove blank rows