Data split - currencies

Hi all,

I currently have a data set where each row has columns of multiple currencies. I need to split this out so each currency has a separate row but I'm struggling on how to do this in the most efficient way. Please see attached document for requirements.

Thanks so much in advance!

Required format LL.xlsx

Datasets

Accepted answers

I would suggest:

First, transpose it to be row-based.

You can then use a Text to Columns tool to split currency and field

After that, a select tool can be used to rename columns

Finally, a Cross Tab puts the rows back to the column format

Sample attached.

CrossTab and Transpose.yxmd

All comments

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

I would suggest:

First, transpose it to be row-based.

You can then use a Text to Columns tool to split currency and field

After that, a select tool can be used to rename columns

Finally, a Cross Tab puts the rows back to the column format

Sample attached.

CrossTab and Transpose.yxmd

Thank you! That works perfectly

Sorry I have another question. How would you then create a CNVUSD row which adds the other balances.

Please see attached document.

Thanks

Required format LL updated.xlsx

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703