cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fayie
New Member

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 @Fayie 

 

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
Fayie
New Member

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

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 @Fayie 

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors