Hi All,
I am using the US census data and am trying to transpose the data however the challenge I am having is that the columns have merged cells. I dont think you can use multi-row since this the data is in the columns. Is there a way to unmerge the column header then repeat the data for each column?
Ideally I would like to have the final data look something like the picture attached
Solved! Go to Solution.
Are you needing all the columns, or just the household estimate column? I only see household estimate in the example PNG
For this specific dataset I only need household however I was hopping to leverage a workflow for other datasets that may have two or more subcolumns.
Im not sure how applicable this will be for your uses but I think it at least gives an idea of one possible way to go about it.
In this case, I imported the file indicating that the first row has data. Then I split out the header row that has the zips and then the row that has the sub type.
I transposed the zips and cleaned them and used a multi-row to copy them down to the merged cells that show us a blank in Alteryx.
I filtered out the sub types to be only the one (or ones) I care about. I also filtered the main data to be only the income group rows I cared about.
After that, I used find/replace and the generic column headers to append the names back to the transposed main data.
Hopefully this makes sense and helps.
This is fantastic. For the intended purposes this seems to have worked Ill have to see how I can scale this in other areas.