Parsing/Restructuring Horizontal Table Data

[JMart2135]

Hello Everyone,

I have been asked to parse and standardize some data to be used for metrics generation. Problem is, whoever set this data up... never meant for it to be used this way.

The data shown in the screenshot is an example of the horizontal table that gets updated so it continues to grow to the right. I need to be able to drop the columns where the [Month] in row 1 has a  "total" value that is null.

Then I need to somehow take the data from each group of three columns ([Month], [Error Code], and [Comments]) and end up with a table with the following headers.

Month, Location Name, Person Name, Desc_Count, Error Code, and Comments.

I need this to auto select any new columns monthly as the table continues to grow. I tried splitting the top 3 rows then the "desc" rows using the sample tool the used the auto field-->auto select tool trick of dropping the booleans, however then my top 3 rows are no longer aligned to try and union them back together. Really stuck here. Almost need to try and drop the null columns then pivot the columns in counts of 3, i think? In the end, hopefully this will only be needed shortly as i try and convince them to change the table layout!

Thanks for the help, it is much appreciated!

[atcodedog05]

Hi @JMart2135 

Yup this easily possible. Can you share that excel file. You need to use transpose and then crosstab.

[JMart2135]

@atcodedog05  Awesome, here it is.

[JMart2135]

@atcodedog05 and i just realized the top row are not the keys they were column names. so i also need to force that row 1 to actually be the headers then do the transpose and crosstab.

[atcodedog05 · Accepted solution]

Hi @JMart2135 ,

Here is the workflow:

Input:

Output:

Workflow:

I hope it meets the expectation. Check and let me know

Hope this helps 🙂

[JMart2135]

Thank you! i was able to get it done. Thanks for the help!

[atcodedog05]

Happy to help 🙂

Cheers and Happy Analysing 😀