How to create null rows grouped by two column?

[DigitalAdam]

Hi All,

I am trying to create a workflow that is grouped by column 1 & 2, and if there are not 5 rows for each grouping then null rows are added to get the total number of rows for each grouping to five. 

I am doing this so I can create a standardized way to work with the data in excel. 

Currently, if there are not rows it will just move onto the next grouping (shown below). 

I'd like it to be adjusted_artist, adjusted_track with Rows 1,2,3,4,5 and then so on. If there aren't 5 values to fill these 5 rows id like the remaining rows to populate as "null" 

Would anyone have any tips on how to accomplish this? 

[skeen503979]

Hi there @DigitalAdam 

If I understand what you're wanting correctly, you'll need to use the Data Cleansing tool under the blue Preparation tab.  This will allow you to replace null values with either a blank or a "0".  After this, you can use the Formula tool to replace the blank or "0" with the text you prefer (which sounds like you want to display the actual word "null").

Start by identifying the data type per column with the Select tool.  Then add the Data Cleansing tool and select each of your 5 rows.  Check the boxes in the "Replace Nulls" section of the tool configuration (depending on the data type).

[mot · Accepted solution]

Hi @DigitalAdam,

You can use Append Fields tool to create a blank table with 5 rows for each column1-column 2 combination. You can then fill the table using join and union. 

I hope this helps. Best!

[DigitalAdam]

Wow this worked perfectly! Thank you so much. I hadn't used some of these utilities before as well so I appreciate the introduction to them!