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 have a question concerining extracting lists in Power Query. So in my query I have multiple columns. One of them ist called "TeamID" an contains numbers. Another one is called "statistics" and contains a number of lists. The data is from a football API.
The lists have a structure like this:
List.Sum(List.Transform([statistics], each if [team_id] = 1079 and Record.HasFields(_, "details") then List.Sum(List.Transform(_[details], each if _[type_id] = 321 then _[value][total] else 0)) else 0))
Now my problem: This code works perfectly. But I don't want to go deep into Power Query to change the team_id everytime I change the team. So instead of the absolute number I wanted to connect this part with my column "TeamID" from the query. Like this...
= List.Sum(List.Transform([statistics], each if [team_id] = @Abfrage3[TeamID] and Record.HasFields(_, "details") then List.Sum(List.Transform(_[details], each if _[type_id] = 321 then _[value][total] else 0)) else 0))
But this doesnt work. Everytime I get "0" as a result. It's the same when I try it with a parameter or a similar field in another query. Maybe some of you will have a hint for me.
Thanks in Advance, Greetings, Marvin
Solved! Go to Solution.
Hi @Mknotes1279
You may try the following code for a new column:
= let vTeamId = [TeamID] in List.Sum(List.Transform([statistics], each if [team_id] = vTeamId and Record.HasFields(_, "details") then List.Sum(List.Transform(_[details], each if _[type_id] = 321 then _[value][total] else 0)) else 0))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Mknotes1279
You may try the following code for a new column:
= let vTeamId = [TeamID] in List.Sum(List.Transform([statistics], each if [team_id] = vTeamId and Record.HasFields(_, "details") then List.Sum(List.Transform(_[details], each if _[type_id] = 321 then _[value][total] else 0)) else 0))
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thank you so much, you're my hero 😍
Why not just parse the JSON with Json.Document to simplify things? You may need to add/remove characters to create valid JSON but should still be easier.
Pat
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
24 | |
23 | |
21 | |
16 |