Is this the best way to unpivot data?

Question

Hello hive mind,I am calling an API that gets me some foreign exchange rate data (all from USD). The data that comes back is in this format:timestamp			AUD			EUR			GBP		2024-04-24 12:00:00			1.538766			0.935636			0.803964		 I’ve highly simplified this. There’s a possible169 currencies here, I’m only showing 3. I need to unpivot it and get it into this format:timestamp			from_ccy			to_ccy			rate		2024-04-24 12:00:00			USD			AUD			1.538766		2024-04-24 12:00:00			USD			EUR			0.935636		2024-04-24 12:00:00			USD			GBP			0.803964		 My current plan looks like this: It is fine for 3 currencies. But not fine for 169!Anyone got any ideas for steps I can use to keep my plan clean and tidy? (And that I don’t have to make 169 copies of Alter Format 😱)

may_kwok · Accepted Answer

It’s amazing what a walk around the block at lunchtime can do for you.

What I ended up doing:

Cartesian join this 1 raw record to my master list of currencies. The list of currencies is in the to_ccy column.
Create the “rate” column
Use Dynamic Expression Assigner step
Get rid of nulls (in case rate supplied is not in my master list, or rate in master list but not supplied)
Tidy up the columns