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,
I am having 5 excel files Sales_2017, Sales_2018, Sales_2019, Sales_2020,Sales_2021 and each excel is having this format of data
The customer Name may be same in any of the files and may be different and also it is possible that sopme customer are not present in any other files.
I want this format of data
The values in 2017, 2018, 2019, 2020, 2021 are the agregated sum of values present in each excel column.
For the customer who is not present in other years there will be null or blank.
First of All, I created a unique customer name table having all the customer names in that . I want to use this table as a Dimension Table.
Now i used merge between this new customer table with each of Sales table to get this format of data
I used the below join
So i performed 5 joins with each of the sales table to get these 5 tables
Till here the data is coming correctly. But now i am stuck to moved ahead. I am confused about what to perform now to get the below format -
If i perform join again between the above tables that i created then this will create multiple duplicate values in the table. We can't perform left outer join because it will loose those customer from the right table which are not in the left one. Same with the right join and if i perform full outer a lot of duplicates are bein created and we can't remove the duplicates from the final table because my source table is containing same customer multiple times with different sales values in the same year as shown
This is the source table Sales_2017 having multiple entries for the same customer
Please suggest how to achieve the requirement
Solved! Go to Solution.
Hi @Amar-Agnihotri ,
Step1 - Append all five files.
Step2 - Unpivot all the value columns (i.e. 2017 - 2021). You will get "Attribute" and "Value" columns
Step3 - Pivot the "Attribute" column
Regards
KT
Hi @Amar-Agnihotri ,
Step1 - Append all five files.
Step2 - Unpivot all the value columns (i.e. 2017 - 2021). You will get "Attribute" and "Value" columns
Step3 - Pivot the "Attribute" column
Regards
KT
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.