As you can see, the columns are shifted to the left, but I'd like to have all columns with Owner Name, Owner ID, Shop ID etc underneath each other. It's fairly random as to how far they're shifted to the left and almost every row is different from the one above. I've got files for several years and it happens in every file, but not necessarily in the same row. I'm inputting all files at the same time with one input tool so they get unioned together, but I've managed to seperate the misaligned rows. I was wondering if there was a way to align them properly in Alteryx.
I suggest making the change to the system that produces this document. It seems that fields with repeated values are left blank. If these fields were populated, this will solve your alignment problem and allow each row to be evaluated on their own. Using your example file, the yellow fields are values that were missing and populating these corrected the alignment problem.
EDIT: That felt like a lazy response, so I've attached a solution that solves this in Alteryx.
The key is to Transpose, reverse the order of the fields, and Cross Tab. This essentially "right-aligns" all of your fields. I tested it out with both sheets in your sample data and it seems to be working. I also took the liberty of playing the the naming portion to get it into a more usable format.
I see what you're doing there, but it doesn't quite work for me. I think it's because of the IsNotEmpty filter. I do have columns which are empty, but the dataset I attached yesterday didn't reflect that. I have attached a more accurate version.
Thank you for renaming the fields, that's very helpful and much more elegant the the solution I came up with.
Since every row is different, I have to split them out into different branches and replace Nulls with a value depending on how far the columns have been shifted to the left. Now I just have to turn the repeating steps into a macro and figure out how to solve my problem if I've got several years in my input data rather than just one as I won't be able to use the sample tool. Thanks so much for your help, I wouldn't have been able to solve this without your input!