Merge multiple files into single based on criteria

[mb0307]

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

[mb0307]

Can anyone help pelase?

[v-yiruan-msft]

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]

@v-yiruan-msft No do not want to convert Output to Change. 

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

Thanks