Transform multiple files in directory then combine them

[mrmmickle1]

Hey there, I'm a brand new Alteryx user but have some experience with Power Query.  I've been unable to solve this issue in Power Query and was hoping that Alteryx will solve my dilemma.  Hoping the below description will be enough to understand my issue.  The data I have is structured exactly as below.  Rather than foods and lunchboxes it's an accounting taxonomy.... which I'm unable to display.

Scenario:

I have a situation where I have data that has a few descriptors above the data table that I would like to keep (I cannot control the format of the files, so it is not an option for me to change the format). In this example I am using only two files but there will be 50 - 60 files in the folder. They will all have the same format as the two in the image. I am trying to combine the data so that it can be aggregated. I have a before and after below as an example. I believe the trick is that I need to complete X number of steps with a file before actually combining the files. I'm not quite sure to accomplish this task. Any help would be very much appreciated. Thank you for taking the time to review my inquiry.

i.e. In my mind these are the steps:

1. Complete x cleaning process for file 1
2. Complete x cleaning process for file 2
2. Complete x cleaning process for file..... etc until end of directory
4. Combine these now individual transformed files....

Here is the before and after image for reference:

[Luke_C]

Hi @mrmmickle1 

I think this is achievable, would you be able to post 2 sample files rather than screenshots?

[mrmmickle1]

Hey Luke-

Sure thing.  Here they are!  Really appreciate the assistance.

[Qiu · Accepted solution]

@Luke_C 
Thank you for asking the data.

@mrmmickle1 
here is my try.
We can also convert the Tools in the container to a Standard Macro, so it will be less messy. 😁

[Luke_C]

Hi @mrmmickle1 

Here's an example of how you can do this with a batch macro. In the attached, the directory tool will read in all xlsx files in the folder path you enter. It will then pass the full path into the macro, which will in-turn update the input tool in the macro to process each file individually. Within the macro I pick out the first 3 rows with the date/names, and then append those to the main data set. 

[mrmmickle1]

Hey Luke and Qiu-

Thank you for taking the time to look at my issue and to help resolve it.  I will need to look into this further as I'm not quite sure how you've completed the task.  Being unfamiliar with the software is a disadvantage of my understanding the what exactly is being completed.  Please let me review a bit further and come back with some additional questions.  Thank you again.  Very kind of you to answer.

[Luke_C]

@mrmmickle1  Not a problem, happy to go into a bit more detail once you've had a chance to look more. Let me know if you have any questions.

[mrmmickle1]

@Luke_C

Hey there after reviewing the macro file I see there are a few things that I don't know where they are at in the interface or had questions:

1. What is the control parameter doing exactly?

2. What is the Update Value?

3. #2 > Does this just specify and example file to use for the transformation?

I believe I understand the other steps however,  where is the control that promoted the fields to headers in the interface (the tool after the Skip 1st 4)

Another question when I updated the file directory values I am still getting repeated results for Lunch Box 1?  It's like the macro processed the same file twice?  Is there something I need to updated for it to iterate through?  I checked the directory a bunch of times... (see below image)

Maybe I didn't update the directory in the right places?

[apathetichell]

https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923

This is a great starting point for batch macros...

[Luke_C · Accepted solution]

Hi @mrmmickle1 

To address your questions in order:

1. What is the control parameter doing exactly? The control parameter dictates what the macro processes each time it executes. In this case, we are reading in a list of file names and using the full path of the file as the control parameter (this is the dropdown in the configuration of the macro). The dropdown links to the control parameter tool in the macro. The steps in the macro are only being processed for one file at a time until it works through all the records from the directory tool.

2. What is the Update Value? The value passed from the control parameter (file path) is used to update the input tool of the macro. In this case we're excluding the tab name since the assumption is that is consistent, and instead swapping in the appropriate filename/path so the input tool knows what to read in.

3. #2 > Does this just specify and example file to use for the transformation? The #2 is just a label of the connection string, it is often used in more complex macros to dictate certain logic in the update values. The input tool in there is just an example file for testing, it has no major bearing on the output when the macro runs in a workflow.

I believe I understand the other steps however,  where is the control that promoted the fields to headers in the interface (the tool after the Skip 1st 4)  This is the gray dynamic rename tool. It is configured to take the column names from the first row of data

It's tough for me to say why the lunch box 1 data is being duplicated, my best guess is the file might be in your directory twice. In the macro you could try selecting the 'output file name as field' option in the input tool to see where the data is coming from more clearly.