The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a set of lists and each one has categorized ColumnNames.
I'm summing the values of each category step by step on PowerQuery, but it's taking a life time, even though my dataset isn't that big.
This is the formula I'm using
= Table.AddColumn(#"Previous Step name", "Total New Column", each List.Sum(Record.FieldValues(Record.SelectFields(_, List with ColumnNames))), type number)
I thought of having a list of lists and change the Column name and the List with ColumnNames dynamically, but I have no idea if I can do it and how to do it.
Each list would require its own Total column.
Start Table
Date | ColA | ColB | ColC | ColD | ColE |
01/09/2023 | 1 | 2 | null | null | 3 |
02/09/2023 | 1 | null | 3 | null | 2 |
03/09/2023 | null | 3 | 2 | 1 | 1 |
listA = {ColA, ColC}
listB = {ColB}
listC = {ColD, ColE}
Result Table:
Date | ColA | ColB | ColC | ColD | ColE | Total listA | Total listB | Total listC |
01/09/2023 | 1 | 2 | null | null | 3 | 1 | 2 | 3 |
02/09/2023 | 1 | null | 3 | null | 2 | 4 | 0 | 2 |
03/09/2023 | null | 3 | 2 | 1 | 1 | 3 | 3 | 2 |
Could someone help me?
Thank you
Solved! Go to Solution.
Hi, @Binoob
You can try the following methods.
In the Power Query-Transform-Unpivot Other Column:
In the Power BI Desktop:
Measure:
Total listA = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute] in {"ColA", "ColC"}&&[Date]=SELECTEDVALUE('Table'[Date])))+0
Total listB = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute] in {"ColB"}&&[Date]=SELECTEDVALUE('Table'[Date])))+0
Total listC = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute] in {"ColD", "ColE"}&&[Date]=SELECTEDVALUE('Table'[Date])))+0
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Binoob
You can try the following methods.
In the Power Query-Transform-Unpivot Other Column:
In the Power BI Desktop:
Measure:
Total listA = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute] in {"ColA", "ColC"}&&[Date]=SELECTEDVALUE('Table'[Date])))+0
Total listB = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute] in {"ColB"}&&[Date]=SELECTEDVALUE('Table'[Date])))+0
Total listC = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute] in {"ColD", "ColE"}&&[Date]=SELECTEDVALUE('Table'[Date])))+0
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.
Your insights and updates will greatly assist others who might be encountering the same challenge.
Hi, @Binoob
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
58 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
74 | |
31 | |
30 | |
24 | |
22 |