Forum Discussion
Data transformation
Hello! I need help with data transformation. Here is an example of my data
I need to understand how to reshape/transform it so that I can use it as a regular data table and create measures and visualizations in Power BI Report Server with all the necessary filters: Year (from 2017-2028), Category (1-3), Country, Region.
Note: The country/region column is not only for individual countries but also for different political and geographical regions.
Hi jenningobo You can reshapre your data using power query. I am attaching file here for your reference. Download and open power query to check steps. Steps are: First, filter out All the totals and only keep categories 1,2,3. Then select country/region, figure, unit column and unpivot other column. This way years will be in one column and value will be in other column. All the regions and country in one column. So, You can create 2 seperate query. One for country and other for region and create relationship between them.
Reshape fileHope this helps!!
If this solve your problem, please accept it as a solution!!
3 Replies
Hi jenningobo You can reshapre your data using power query. I am attaching file here for your reference. Download and open power query to check steps. Steps are: First, filter out All the totals and only keep categories 1,2,3. Then select country/region, figure, unit column and unpivot other column. This way years will be in one column and value will be in other column. All the regions and country in one column. So, You can create 2 seperate query. One for country and other for region and create relationship between them.
Reshape fileHope this helps!!
If this solve your problem, please accept it as a solution!!Hello Shafiz! Thank you for your assistance! Looks like it works. I have created separate queries for Revenue, User, and ARPU. Could you please suggest the relationships between these tables? Do I need to create a DateTable?
Since you have created 3 separate queries, to develop relationship among them, I would suggest just create a seperate query for year (Unique) only (You could create Date table , but won't serve purpose). Also, country/region (Unique) is other dimension you need to develop relationship.
Hope this helps!!
If this solved your problem, please mark it as a solution!!
