Formatting Uploaded Data

[rajibmahmud]

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

[MFelix · Accepted solution]

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

[MFelix · Accepted solution]

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

[rajibmahmud]

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?

[MFelix · Accepted solution]

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

[prakash_gautam]

Please take a look at the screen below: