Forum Discussion
Creating a new table help
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 |
-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///
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.
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.
8 Replies
-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///
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?
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.
