Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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?
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsPlease take a look at the screen below:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |