topic Re: Data transformation in Report Server

Data transformation

jenningobo — Tue, 20 Aug 2024 07:32:12 GMT

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.

Re: Data transformation

shafiz_p — Tue, 20 Aug 2024 10:52:00 GMT

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 file

Hope this helps!!

If this solve your problem, please accept it as a solution!!

Re: Data transformation

jenningobo — Wed, 21 Aug 2024 12:08:01 GMT

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?

Re: Data transformation

shafiz_p — Thu, 22 Aug 2024 04:25:06 GMT

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!!