Creating a new table help

[evvvb7]

Hello! Please help - I have a situation where my data source has 1 row of sales data and separate columns identifying the the types of product. The issue is I need to display analytics based on the Product Type summarizing the Amount of each Product Type sold. I need to create a new table, I beleive, to achieve this so Product Type is in a singular column, so that there is multiple rows for each sale #, one for each Product Type. Such as transforming this sample data:

Sale #	Amount	Product A	Product  B	Product C	Product D
1	200,000	Yes	No	No	No
2	15,000	No	Yes	Yes	No
3	22,000	Yes	No	Yes	Yes

To this:

Sale #	Amount	Product Type
1	200,000	Product A
2	15,000	Product B
2	15,000	Product C
3	22,000	Product A
3	22,000	Product C
3	22,000	Product D

[kpost · Accepted solution]

-Click"Transform Data" to open Power Query and modify the table

-Highlight the first two columns "Sale #" and "Amount", then right click and select "Unpivot other columns"

- Filter the "Yes/No" column on the "Yes" value.

See below screenshot, and I have also attached a .pbix file where I solved your problem.

///Mediocre Power BI Advice, but it's free///

[kpost · Accepted solution]

Yes, if you want to keep the original dataset untouched, then BEFORE you do the steps I outlined, duplicate the original table.  Make your modifications to the duplicate.

 

[kpost · Accepted solution]

-Click"Transform Data" to open Power Query and modify the table

-Highlight the first two columns "Sale #" and "Amount", then right click and select "Unpivot other columns"

- Filter the "Yes/No" column on the "Yes" value.

See below screenshot, and I have also attached a .pbix file where I solved your problem.

///Mediocre Power BI Advice, but it's free///

[evvvb7]

Thank you! This certainly does what i was looking for.

Is there a way of doing this without changing the original dataset? Such as creating a table rather than overriding the table? Perhaps as a query so when data is updated it automatically updates?

[kpost · Accepted solution]

Yes, if you want to keep the original dataset untouched, then BEFORE you do the steps I outlined, duplicate the original table.  Make your modifications to the duplicate.

 

[evvvb7]

Thanks for providing more insight and apologies for delay on my response (weekend)...

Is there a way I could have the "unpivot columns" do its things upon updating a dataset? If the underlying data gets refreshed, this unpivot function automatically updates the new table we created for this scenario? Would it be a query as opposed a table creation?

[kpost]

Anything you do in Power Query will automatically be executed when you update the dataset.  It's baked into the 'refresh'.

In the case of a 'Duplicate' operation in Power Query, I believe the first step for both tables is to query the original source before doing any transformations.

[evvvb7]

So query the first table, and then do the unpivot columns off the querried table?

[kpost · Accepted solution]

Assuming you want to have two copies of this table in your dataset, namely the original and the transformed one per your question:

Add the table to your dataset, Duplicate it, do the transformations I described in the Solution on the duplicated version.

This will leave you with two copies of the table.  One transformed per your question, and the other left untouched.  Both will query the original source, and both will update when the source document is updated and you refresh your dataset.

[evvvb7]

Awesome, thanks for your expertise!