I am inputting multiple excels at once, auto-config by position (can't auto-config by name due to irregularities in the files). While the inputs are supposedly of somewhat similar format, they're not really, so I have to standardize them. Some of my inputs look like this:
F1 | F2 | F3 | F4 |
George | 423242 | 12/03/2012 | |
1.86m | 432352 | 12/05/2020 | |
but on some of my inputs, for whatever reason, the data is preceded by an empty column, such as this
F1 | F2 | F3 | F4 |
Mike | 423425 | 15/05/2013 | |
1.73m | 525623 | 30/08/2017 | |
What kind of tool/formula/rule can I use to make sure the inputs where the entire 1st column is empty, all the other data is shifted one column to the left, so that I can unionize it with the other data and process it as 1 stream? Note that the data should only be shifted if the entirety of column F1 is empty. Not wherever a single cell of F1 is empty
Solved! Go to Solution.
In Alteryx Designer 2020.2, the Data Cleanse Tool has been updated with options to remove null columns or rows.
You can also do this by transposing the data set, filter [Value] with an !IsNull() or !IsEmpty() statement and CrossTab back to the original format. If you do this you should add a RecordID first and mark that as a Key field in the Transpose and also Group by in the CrossTab.
Hi @DavidP , unfortunately I can't upgrade to that version.
Could you attach a sample workflow using the method you describe? The transpose tool is something I still need to work on a bit
Thank you! it is really charming solution 🙂 and it helped me a lot!