JSON Parse to table

[JarkkoP]

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...

[atcodedog05]

Hi @JarkkoP 

Here is how you can do it.

Workflow:

Hope this helps : )

[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 : )

[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 · Accepted solution]

Hi @JarkkoP 

Try this approach:

[atcodedog05 · Accepted solution]

Hi @JarkkoP 

My take on it. Should be dynamic too.

Workflow:

Hope this helps : )

[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!

[JarkkoP]

Thanks, I was struggling with which one to accept 😄 Both are not approved as solutions :=)