Dynamic Column Name with Merged Cells

[bburzanko]

I have the sample file attached that has Year / Month / Actual-Budget information. I am trying to get to a point to have the column names pulled from the first 4 rows to have the column name be 2023-2-November Actual, 2023-2-November Budget, 2023-3-December Actual, 2023-3-December Budget, etc...

The merged cells are causing an issue for me to allow for this and the periods change so I cant just manually rename.

Has any one dealt with this issue and what was the work around you used for it?

The file attached has the input and end goal.

[Carolyn · Accepted solution]

Hi! Take a look at this and see if it works. 

What I did was separate the data into the first 4 rows = headers and then skipping the first 4 rows

1. Set the Input Tool to have "first row contains data" so the column headers are called F1, F2, etc, and the years starts in Row 1
2. Sample Tool to get the first 4 rows which contain the different column header values
3. Flip the first 4 rows into columns
4. Use Multi-Row Tool to fill in nulls - this is the trick to get around your merge problem
5. Summarize to Concatenate based on Column # (F1, F2, etc)
6. Put the now-single-column of column headers into a row so it could be Unioned with the rest of the data
7. Dynamic rename to take the now-single-column of column headers into the proper column header position