Forum Discussion

mb0307's avatar
mb0307
Icon for Responsive Resident rankResponsive Resident
3 years ago

Merge multiple files into single based on criteria

Hi all,

 

EXCEL DATA ATTACHED 

 

I have miltiple INPUT TAB in this format:

 

 

A product CHANGE TAB, shows when a NEW LINE will be created in OUTPUT, based DATE RANGE on change in LOCATION or PRODUCT CODE:

 

 

So OUTPUT TABLE in Power BI will look like this:

 

The Output Table, highlighted in Red shows how the new lines will be created using CHANGE TAB.

 

Please any help with this will be highly appreciated.

 

Thanks

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi mb0307 ，

    Do you want to convert the Output table to Change table in Power BI? If yes, you can merge these columns named with date in Power Query Editor first.

    Then create some calculated columns to get the field [Location],[Product Code Swap],[Transition From Week] and [Transition To Week]. I just create a sample pbix file(See the attachment) and achieve part of above steps. I need you provide more details on how to get the previous fields. What's their calculation logic? Thank you.

    Best Regards

    • mb0307's avatar
      mb0307
      Icon for Responsive Resident rankResponsive Resident
      3 years ago

      Anonymous No do not want to convert Output to Change. 

       

      OUTPUT will be generated as a table.  So Need to build OUTPUT as shown.

       

      Thanks