Create a custom table duplicating rows from another table based on the other table's headers

[pxb015]

Hi, I'm asking for help creating a custom table which creates multiple rows out of each original row in my PowerBI dataset. Please excuse me if this has already been answered.

The scenario:

I imported an excel file into my project with this table (let's name it "Customers"):

Customer	BoughtInStore	BoughtOnline	BoughtByReferral
Bob	Y	Y
Jane	Y	Y	Y
Jenny	Y

Without leaving PowerBI and creating external excel files, I want to create a new query or table (not a measure) in my project which looks like this:

Customer	Stage
Bob	BoughtInStore
Bob	BoughtOnline
Jane	BoughtInStore
Jane	BoughtOnline
Jane	BoughtByReferral
Jenny	BoughtInStore

My new table will create a row for each customer and for each stage that they've been marked in the original Customers tables.

How can I acheve this?

Thank you in advance!

[ebeery · Accepted solution]

@pxb015 this can be easily achieved using the "Unpivot" transformation in Power Query.

After importing your table, select the "Customer" column, and from the Transform tab, select "Unpivot Columns" >> "Unpivot Other Columns".

Then, filter the "Value" column to keep only "Y", and finally, remove the Value column.

[pxb015]

Amazing, this is exactly what I needed. Thank you very much!