Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |