Unique Tables into Columnar Data Range

[BeerGuy]

I am trying to convert a data file comprised of many small tables into columns of data for easy use. I am still relatively new to Alteryx, and this is a bit out of my comfort zone, as it is not too simple. Tables such as the ones below go down ~20K rows:

I need to convert to a data table like this, where each individual rate is a unique row, characterized by other the data entries:

Year	Month	Term	Type	Periodicity	Rate
2023	January	Short-Term	AFR	Annual	4.50%
2023	January	Short-Term	AFR	Semiannual	4.45%
2023	January	Short-Term	AFR	Quarterly	4.43%
2023	January	Short-Term	AFR	Monthly	4.41%
2023	February	Mid-Term	Adjusted AFR	Annual	2.89%
2023	February	Mid-Term	Adjusted AFR	Semiannual	2.86%
2023	February	Mid-Term	Adjusted AFR	Quarterly	2.85%
2023	February	Mid-Term	Adjusted AFR	Monthly	2.84%
2023	March	Long-Term	Adjusted AFR	Annual	2.84%
2023	March	Long-Term	Adjusted AFR	Semiannual	2.82%
2023	March	Long-Term	Adjusted AFR	Quarterly	2.81%
2023	March	Long-Term	Adjusted AFR	Monthly	2.80%

I know there is an element of splitting the merged/title rows into columns based on a ">" delimiter, and then transposing different rows into columns. But, hoping someone with more experience will know how to do all of these in the appropriate order, I have really struggled with this one.

Thanks so much!

[binuacs]

@BeerGuy would you be able to provide a sample input file?

[BeerGuy]

Sure - here is attached data, edited from original form as the full data set is about 10X as many tables. Thanks in advance for taking a look!

[mceleavey · Accepted solution]

Hi @BeerGuy , love the name.

This is fairly simple if fiddly.

I first created a table identifier using a simple multi-row formula:

I then split the stream into header and detail lines using a filter.

The header line I simply split using text to columns and ended up with the following:

For the detail lines the original format is kind of header row header row, so I created a 1,2 identifier and pivoted the data around the table and row ID.

Now I simply created the four identifiers for each of the four columns of each "row", then used a filter to seperate them and join them back together so they were all on the same row. I ended up with:

...which I simply joined to the header stream on the table number:

Workflow attached.

I hope this helps.

M.

[BeerGuy · Accepted solution]

@mceleavey thank you so much!!

This is a massive help, really appreciate it! Solved!

[mceleavey]