The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Can you tell me if your problem is solved? If yes, please accept it as solution.
Regards,
Nono Chen
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the July 2024 Fabric update to learn about new features.
User | Count |
---|---|
14 | |
7 | |
4 | |
2 | |
2 |