How do I fill horizontally (across columns but within the same row)? I need the solution to be generic. So setting the values to a hard coded string is not sufficient.

[Trifacta_Alumni]

Hmm, what did you call the column you populated with SOURCEROWNUMBER() upstream of the unpivot-fill-pivot sequence?

[446193eada4dc478fb22]

OriginalRowId – see Wrangle file attached to previous email. Please note that I am working in Dataprep (in case that makes a difference). Harry Burn

[Trifacta_Alumni]

Hi Harry,

Unfortunately, I am unable to see any attached files in this thread. However, I have just verified that the approach we are discussing applies to Dataprep.

If the column you created with the values of SOURCEROWNUMBER() is called OriginalRowId, then replacing 'row_num' with 'OriginalRowId' in the 'Columns to sort rows by' field of the step editor for the Fill transform should do the trick.

Best regards,

Nathanael

[446193eada4dc478fb22]

Hi Nathanael, Please see attached. I used SOURCEROWNUMBER in the first step in my recipe. After this I unpivoted to transpose the data. Once the unpivot is complete, the OriginalRowId can no longer be used for the order clause of the FILL function because, instead of being a column name, it is not a value within the ‘key’ column of the unpivoted data. I will be grateful for any insight. I must be doing something wrong, but cannot see what it is. Thanks again Harry Burn

[Trifacta_Alumni]

Hi Harry,

I still cannot see any attachment, but I think I understand the problem. Try this:

1. Open the Recipe Editor
2. Mouse over the un-pivot step, and click the edit icon
3. Remove OriginalRowId from the list of columns being un-pivoted
4. Click Add.

You should now be able to use OriginalRowId downstream in the custom sort.

Best regards,

Nathanael

[446193eada4dc478fb22]

Hi Nathanael, I have added my wrangle. It seems that when you do this via email, it does not work.

In response to your last suggestion, with the unpivot, there are 26 columns prior to the UNPIVOT action. So I have not listed them all individually. Instead, I used ' * ' as the Column input. I tried to use RANGE(2,26,1) to reference all columns after OriginalRowId, but the input box only accepts explicit column names.

[Trifacta_Alumni · Accepted solution]

Switch to column view, select the OriginalRowId column, then click Invert Selection:

From the ribbon across the top, select Unpivot. This pre-populates the step with all the columns except the one that will be used later for sorting:

[446193eada4dc478fb22]

Hi Nathanael,

Thanks a lot. This definitely solves the problem. Really appreciate it.

I suppose it would be better if there was a generic way to capture all columns except OringinalRowId. So that, when a file comes in next month with an extra column for the extra month, it could be processed by the same recipe. However, this solution works very well if you have the same number of columns each month.

All the best

Harry

[Trifacta_Alumni]

Hi Harry,

Huzzah!

Good discussion. I will be submitting the following enhancement requests on your behalf:

• A kinder, gentler and more intuitive way to fill horizontally
• A way to refer to a range of columns in the step editor apart from '*', e.g., all except this one, etc.

If I have missed or mis-represented anything, please let me know.

Best regards,

Nathanael

[446193eada4dc478fb22]

Yes, those enhancements will benefit the user a lot. I can't think of any others resulting from this use case at the moment.

Thanks again