Forum Discussion
Formatting Uploaded Data
Hi All,
Currently I am getting automated sales data from a system to PowerBI in below format. However I need to transform data into a different format where Quarter will be in Column instead of rows. As rest of the data feed is in that format. Its not possible to do it in excel before data feed as the amount of data is huge, and I dont have option to deploy someone to do data clean up.
Can someone help?
Current format
| Region | Year | Quarter | Value |
| USA | 2014 | Q1 | 100 |
| USA | 2014 | Q2 | 50 |
| USA | 2014 | Q3 | 251 |
| USA | 2014 | Q4 | 213 |
| USA | 2015 | Q1 | 155 |
| USA | 2015 | Q2 | 123 |
| USA | 2015 | Q3 | 5 |
| USA | 2015 | Q4 | 61 |
Desired format
| Region | Year | Q1 | Q2 | Q3 | Q4 |
| USA | 2014 | 100 | 50 | 251 | 213 |
| USA | 2015 | 155 | 123 | 5 | 61 |
Hi rajibmahmud,
In the Query Editor select the Quarter column and then Pivot columns and select the Value column in the options you will get the desired result:
Regards,
MFelix
Hi rajibmahmud,
You can do this in one of two ways in the query editor:
1 - Create a new column with a text like this:
Column "TEXT" & [Column]
Then delete the previous column and keep this
This can be done by the column from example and it pretty easy to achieve:
2 - Do a Replace in the specific column for the text that you want to add:
If all your rows start with 1 you can do a replace of 1 for Text1
Regards,
MFelix
4 Replies
Hi rajibmahmud,
In the Query Editor select the Quarter column and then Pivot columns and select the Value column in the options you will get the desired result:
Regards,
MFelix
Thanks a lot. MFelix
Another query I have which might going to make my life easier.
Is there any option to add ' infront of all text in a specific column from query editor during data load? or do a look up and replace the text of the column with new vlookup data?
Please take a look at the screen below:
