JSON Parse to table

HI Community,

Im struggling with a JSON converting problem that I cannot figure out. I have a JSON extract as below, that has exchange rates for specific dates and currencies. The data is structured so, that the Date and value is on top, and the currency information is below the actual data. the 0,1 ect defines the data groups. How can I convert this to the following format? All help is much appriciated! :=)

Date	Value	Currency	Currency denom	CurrencyNameFi	ECBpublished
13.01.2022	9,99333	NOK	EUR	Norjan kruunu	True
14.01.2022	9,986	NOK	EUR	Norjan kruunu	True
13.01.2022	10,238	SEK	EUR	Ruotsin kruunu	True
etc...

example.xls

Datasets

Expression

Accepted answers

OllieClarke

Hi @JarkkoP

Try this approach:

JSON.yxzp

atcodedog05

Hi @JarkkoP

My take on it. Should be dynamic too.

Workflow:

Hope this helps : )

JSON Parse (2).yxmd

All comments

atcodedog05

Hi @JarkkoP

Here is how you can do it.

Workflow:

Hope this helps : )

JSON Parse.yxmd

JarkkoP

Thanks Atcodedog05!

I tested the workflow, but it only brings the first date of the dataset (13.1.2022) The dataset has multiple dates, in the example there is 14.01.2022, but once the workflow works, there will be hundreds).

Any ideas how to get the other datasets included also?

atcodedog05

Hi @JarkkoP

Lets bring in all and later you can keep the required.

Workflow:

Hope this helps : )

JSON Parse.yxmd

JarkkoP

Almost there :=) But the problem is where I ran into before, is how do I transpose the table so that the 2 different dates for the same currency go on their own rows and the currency and other lables are copied. as in the example above.

Date	Value	Currency	Currency denom	CurrencyNameFi	ECBpublished
13.01.2022	9,99333	NOK	EUR	Norjan kruunu	True
14.01.2022	9,986	NOK	EUR	Norjan kruunu	True
13.01.2022	10,238	SEK	EUR	Ruotsin kruunu	True
etc...

OllieClarke

Hi @JarkkoP

Try this approach:

JSON.yxzp

JarkkoP

Big thanks OllieClarke & Atcodedog05! both of the solutions do the trick! :=)

atcodedog05

Happy to help : ) @JarkkoP

Also, you are allowed to mark multiple posts as solutions so feel free to 🙂

Cheers and have a nice day!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15872

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703