Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Transforming Data from Rows into Unique Columns

Is this possible to be done in Power Query?

From a huge dataset like this which contains Dates, Names and Events

DateNameEvent
01/01/2022Name1Event 1
01/01/2022Name1Event 2
01/01/2022Name1Event 3
01/01/2022Name1Event 4
01/02/2022Name1Event 5
01/02/2022Name2Event 1
01/01/2022Name2Event 2
01/01/2022Name3Event 1
01/02/2022Name3Event 3
01/02/2022Name4Event 4
01/02/2022Name5Event 5

 

Into something like this

DateNameEvent1Event2Event3Event4Event5
       

Where there is only 1 Date to 1 Name and all it's Events are now Columns.

1 ACCEPTED 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. 

vjingzhang_0-1668765346468.png

 

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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Sorry, looks like the format was slighly changed.

Fayie_0-1668617737931.png

into

Fayie_1-1668617746842.png

 

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

Microsoft Employee
Anonymous
Not applicable

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. 

vjingzhang_0-1668765346468.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors