Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Is this possible to be done in Power Query?
From a huge dataset like this which contains Dates, Names and Events
Date | Name | Event |
01/01/2022 | Name1 | Event 1 |
01/01/2022 | Name1 | Event 2 |
01/01/2022 | Name1 | Event 3 |
01/01/2022 | Name1 | Event 4 |
01/02/2022 | Name1 | Event 5 |
01/02/2022 | Name2 | Event 1 |
01/01/2022 | Name2 | Event 2 |
01/01/2022 | Name3 | Event 1 |
01/02/2022 | Name3 | Event 3 |
01/02/2022 | Name4 | Event 4 |
01/02/2022 | Name5 | Event 5 |
Into something like this
Date | Name | Event1 | Event2 | Event3 | Event4 | Event5 |
Where there is only 1 Date to 1 Name and all it's Events are now Columns.
Solved! Go to Solution.
Hi @Anonymous
The feature you want is Pivot columns in Power Query. Select "Event" column and click on Pivot Column. Choose an appropriate column as Values column. You can expand Advanced options to change the aggregation type of the values column.
If you want to use this query as a mega dataset that will be used in multiple final reports or datasets, I would suggest that you create a Power BI dataflow to prepare this table. Power BI dataflow can let you transform, load and refresh data at one place. Then you can connect to this dataflow from Power BI Desktop to make multiple different reports. You can also transform data further in Power Query Editor.
You can read When to use dataflows section in the following document. I'm sure it is what you want. And Power BI dataflow supports Power Query too.
Introduction to dataflows and self-service data prep - Power BI | Microsoft Learn
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Sorry, looks like the format was slighly changed.
into
You should consider not transforming your data like that. It is already set up well for analysis. You can easily make the view you are looking for in a matrix visual by putting the Event column as the columns in the visual.
Pat
Right, I really thought so too.
I thought maybe, just maybe if I can transform all my raw datas into the same view and have it under 1 mega dataset, then Transform it into however it needs to be for the final set, it would refresh faster as it will only grabs and reference from that mega table set as opposed to having multiple raw datas and the transformation for all (for the pivot tables) grabs bits of pieces from all of them.
Maybe i'm not making much sense here, let me know and once I get home, i'll draw up a quick sample.
Thank you very much for replying and giving me your insight!
the same raw data multiple times.
Thanks for the input, may I
Hi @Anonymous
The feature you want is Pivot columns in Power Query. Select "Event" column and click on Pivot Column. Choose an appropriate column as Values column. You can expand Advanced options to change the aggregation type of the values column.
If you want to use this query as a mega dataset that will be used in multiple final reports or datasets, I would suggest that you create a Power BI dataflow to prepare this table. Power BI dataflow can let you transform, load and refresh data at one place. Then you can connect to this dataflow from Power BI Desktop to make multiple different reports. You can also transform data further in Power Query Editor.
You can read When to use dataflows section in the following document. I'm sure it is what you want. And Power BI dataflow supports Power Query too.
Introduction to dataflows and self-service data prep - Power BI | Microsoft Learn
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.