Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aakif_aslam
Helper I
Helper I

Automate Transformation

hello,

 

I have two datasets in Excel, one with daily consumption of resources and other with monthly summary. In daily data I have details of Qty at a individual component level but not its price as per comsumption. Similarly in monthly summary there is comsumed Qty and Price but in a summarized manner based on RID. Both the datasets have one unique column RID. 

Basic requirement is to have Price at a component level.

  • My current approach is to append all the daily data in single excel sheet and lookup Price column from different monthly summary tables loaded from different Excel sheets to maintain One-to-Many relationship.
  • This price is based on the RID so using lookup does not filter Prices based on month period, Price keeps on repeating on RID level. I manually have to Lookup multiple monthly Price columns from different summary data and then extract only values filtered by period and merge all the price columns.  I use the avg. Price for further calculations and obtain the required output.
  • Appending all the monthly summary tables removes the uniqueness as the same RID repeats every month, so for two months there are two duplicate RIDs. This creates Many-to-Many Relationship.

I will append the new data in Excel but want to automate this process and avoid current manual transformations.


 

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @aakif_aslam ,

 

Please provide some simple sample data and expected results.

Creating relationships based on RID seems to work.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors