Help with multi join and transposing data please!

[Sfava]

Hello community, I need a little help please. I have 40-50 files each day that need to be mass imported (the # varies), amalgamated and transposed. I thought this would be relatively simple but am encountering a few issues.

Specifics:  Each sheet has the same number of rows (87 with exact name match across files) and same 3 key columns (Filed ID, Screen and Label). The other columns represent new bookings and the quantity will vary file to file.

The first issue is the multi join ends up with the 3 key columns duplicated several times. If it was always the same number of files I could use the Sort function to just deselect them but the number varies every day... I really just need the first 3. I tried importing all with a wildcard (my preference) but that turned out even worse...

The second problem is transposing the results so that Field ID, Label and bookings become the rows

I'm attaching sample data and the results I'm expecting.

This is what my flow currently looks like: 

Any help would be much appreciated. 

Thank you very much

Shauna

[Qiu · Accepted solution]

@Sfava 
I prepared a batch macro for this purpose.

Tthe label naming needs some adjustment based on your requirement.

[Sfava]

@Qiu Thank you so much for the quick response.

I can't say I fully understand what's happening in the first part of the macro but when I brought in new files it worked!

One question on the macro, when I run it on it's own I get the following message not matter how many times I choose the same file that's on my desktop. Should I be concerned?

Thank you again so much for the help!

[Qiu]

@Sfava 
There is a configuration for the template of Dynamic Input.
Please modify the path and file to your own.