Flattening Data & Dynamically Rename Fields

Nathan_Boynton

Hi Everyone! 

I am having trouble trying to flatten out some data.

I would like to be able to take data that is structured like this:

And output something like this:

Also, I am trying to have the output's headers in column's D, E, & F to dynamically rename based on the input data's rows 3, 4, and 5. Another way of saying this is that I would like the output columns D, E, F (and more) to be dynamically renamed using the following convention "Bucket - Type - Metric", that uses the labels from the associated input data's rows. 

I have attached a spreadsheet with the inputs and outputs to the post. I really appreciate any help! 

Juice Projection and Actuals.xlsx

atcodedog05

Hi @Nathan_Boynton 

Here is how you can do it.

Workflow:

1. In input tool configure first row contains data.

2. Using multirow formula split the headers and body into blocks. I am using Juice as seperator.

Upper branch

3. Converting columns to rows.

4. Groupby column position and concating the names.

Lower branch

3. Converting columns to rows.

4. Joining the names based on column position names.

5. Using regex to split the info needed.

6. Using crosstab to convert rows to columns.

7. Using dynamic replace tool to fix the column name.

This approach is dynamic.

Hope this helps : )

flatenize data.yxmd