Transform data for a dynamic date column

[udles5666]

Hi Community,

I am stuck with a problem to solve and was hoping if someone can help out. 

I have a file coming in every week & only the date (header) column is going to change. Rest the format of the file is the same. I need to add date column to the output & rename the columns which have dates in them. 

Attached is how input and output looks like. I had a crack at it but unsure how it will work end to end.

Can someone please help out? Thanks in advance,

[ArtApa · Accepted solution]

Hi @udles5666 - This is one way how you can do this:

[elhumano · Accepted solution]

Hello,

You can use the Dynamic Rename tool to rename the SALES and VOLUME columns. You can use the Transpose tool to pivot the SALES column for the first row only and then use Text to Columns tool to extract the week. Please see attached workflow.

Cheers,

Joseph

[udles5666]

Thanks @ArtApa & @elhumano! 

[elhumano]

Thank you, @udles5666 (I can't really tag you because I have not yet earned the Asteroid badge) for giving me the opportunity to post my first solution and also learn something new (Find Info tool and the use of regexp_match in the Formula tool used by @ArtApa). 😀