Power BI Data Transformation Question

[Anonymous]

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:

Many thanks

Kind regards

Hannah

[v-xiaoyan-msft]

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 · Accepted solution]

@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 

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

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

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

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

[Anonymous]

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

[amitchandak]

@Anonymous , Unpivot last 4 column in power query

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