Forum Discussion
Build array columns in a dataflow
Dear community,
I am trying to move data pipelines from Azure Data Factory (ADF) to Fabric Data Factory (FDF).
I am inputting exchange rates data in the form of xml adn I need to output as JSON (with a couple transformations in between) to make an API call as a final step.
For the transformation part, in ADF I was usin a mapping dataflow to achieve the needed transformations. In FDF the equivalent is Dataflow Gen 2, so basically Power Query.
A sample of my data in the dataflow is as below:
At this point I am stuck, because I need to aggregate by provider, date and baseCurrency (they are the same for all rows) and then build an array column of objects, each object being a pair of the currency and its respective rate, something like this:
[
{
"date": "2024-07-10",
"provider": "EXT6",
"baseCurrency": "EUR",
"exchangeRates": [
{
"currency": "USD",
"rate": 1.0825
},
{
"currency": "JPY",
"rate": 174.79
},
{
"currency": "BGN",
"rate": 1.9558
},
{
"currency": "CZK",
"rate": 25.379
},
{
"currency": "DKK",
"rate": 7.4603
},
{
"currency": "GBP",
"rate": 0.84518
},
{
"currency": "HUF",
"rate": 392.68
},
{
"currency": "PLN",
"rate": 4.2555
},
{
"currency": "RON",
"rate": 4.9734
},
{
"currency": "SEK",
"rate": 11.4075
},
{
"currency": "CHF",
"rate": 0.9723
},
{
"currency": "ISK",
"rate": 149.1
},
{
"currency": "NOK",
"rate": 11.607
},
{
"currency": "TRY",
"rate": 35.5705
},
{
"currency": "AUD",
"rate": 1.6056
},
{
"currency": "BRL",
"rate": 5.8435
},
{
"currency": "CAD",
"rate": 1.4758
},
{
"currency": "CNY",
"rate": 7.8763
},
{
"currency": "HKD",
"rate": 8.4554
},
{
"currency": "IDR",
"rate": 17557.93
},
{
"currency": "ILS",
"rate": 3.9574
},
{
"currency": "INR",
"rate": 90.4015
},
{
"currency": "KRW",
"rate": 1498.46
},
{
"currency": "MXN",
"rate": 19.2904
},
{
"currency": "MYR",
"rate": 5.0883
},
{
"currency": "NZD",
"rate": 1.7804
},
{
"currency": "PHP",
"rate": 63.118
},
{
"currency": "SGD",
"rate": 1.4605
},
{
"currency": "THB",
"rate": 39.343
},
{
"currency": "ZAR",
"rate": 19.5581
}
]
}
]
Not sure how to achieve this, I am not so familiar with Power Query M, maybe there is a way?
I would liek to avoid using a Notebook for the moment.
Hi joseanmarsol ,
if my understanding is correct, you need the JSON as a textual representation in a column in dataflow to use it in a later pipeline action to make that API call?
Then the solution from Anonymous won't work, as it returns a binary that cannot be loaded properly into a lakehouse table.
But with a small adjustment, you will get it as needed:let Source = ExchangeRates, #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"provider", "date", "baseCurrency"}, { {"exchangeRates", each Table.SelectColumns(_, {"currency", "rate"})} }), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "JSON", each Text.FromBinary(Json.FromValue([exchangeRates]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"exchangeRates"}) in #"Removed Columns"I don't know about the size limits for fields in fabric lakehouse, but if you have really large tables, you might want to check for that.
3 Replies
Hi joseanmarsol ,
if my understanding is correct, you need the JSON as a textual representation in a column in dataflow to use it in a later pipeline action to make that API call?
Then the solution from Anonymous won't work, as it returns a binary that cannot be loaded properly into a lakehouse table.
But with a small adjustment, you will get it as needed:let Source = ExchangeRates, #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"provider", "date", "baseCurrency"}, { {"exchangeRates", each Table.SelectColumns(_, {"currency", "rate"})} }), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "JSON", each Text.FromBinary(Json.FromValue([exchangeRates]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"exchangeRates"}) in #"Removed Columns"I don't know about the size limits for fields in fabric lakehouse, but if you have really large tables, you might want to check for that.
Hi joseanmarsol
Try the following code:
let Source = ExchangeRates, #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"provider", "date", "baseCurrency"}, { {"exchangeRates", each Table.SelectColumns(_, {"currency", "rate"})} }), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "JSON", each Json.FromValue([exchangeRates])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"exchangeRates"}) in #"Removed Columns"Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi joseanmarsol
Can you tell me if your problem is solved? If yes, please accept it as solution.
Regards,
Nono Chen
