Forum Discussion
Creating a new table help
-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.
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.
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?
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.
So query the first table, and then do the unpivot columns off the querried table?
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.