Transform data for a dynamic date column

Question

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,

Desired_Output.xlsx

Single Source Test.xlsx

Need Solution.yxmd

ArtApa · Accepted Answer

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

elhumano · Accepted Answer

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