Build array columns in a dataflow

[joseanmarsol]

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.

[v-nuoc-msft]

Hi @joseanmarsol 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[ImkeF · Accepted solution]

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 @v-nuoc-msft  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.

[v-nuoc-msft]

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.