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

Power BI Data Transformation Question

Hi There,

I have the first data set in excel (an example) which I need to transform it to the second table beneath. Do you know how I can make it in Power Query:

 

hy201909_3-1637938345603.png

 

Many thanks

Kind regards

Hannah

1 ACCEPTED SOLUTION
negi007
Community Champion
Community Champion

@Anonymous in this case, you will have to unpivot and pivot your data. below is the sequence that you can follow

 

1. Your data is in below format. Open your data in power query window 

negi007_0-1637942201091.png

 

2. then select the last 4 columns of your data and then go to unpivot columns and select unpivot only selected columns. Your data will transform into below format

 

negi007_1-1637942235310.png

 

3. now you can split column2 basis space delimiter, your data will be in below format

negi007_2-1637942378947.png

 

4. last option now you need to pivot the data back, select last two columns and choose pivot column option. your data will be converted into below format which you need here

negi007_3-1637942413031.png

 

for your reference, i am attaching the pbix sample file which you can use to go step by step. let me know if it helps you. thanks

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

View solution in original post

4 REPLIES 4
v-xiaoyan-msft
Community Support
Community Support

Hi @Anonymous ,

 

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

 

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

negi007
Community Champion
Community Champion

@Anonymous in this case, you will have to unpivot and pivot your data. below is the sequence that you can follow

 

1. Your data is in below format. Open your data in power query window 

negi007_0-1637942201091.png

 

2. then select the last 4 columns of your data and then go to unpivot columns and select unpivot only selected columns. Your data will transform into below format

 

negi007_1-1637942235310.png

 

3. now you can split column2 basis space delimiter, your data will be in below format

negi007_2-1637942378947.png

 

4. last option now you need to pivot the data back, select last two columns and choose pivot column option. your data will be converted into below format which you need here

negi007_3-1637942413031.png

 

for your reference, i am attaching the pbix sample file which you can use to go step by step. let me know if it helps you. thanks

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Anonymous
Not applicable

Thank you very much for your help. It works perfectly! 

amitchandak
Super User
Super User

@Anonymous , Unpivot last 4 column in power query

 

https://radacad.com/pivot-and-unpivot-with-power-bi

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.