Sum values based on list of lists with column names

[Anonymous]

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

[v-zhangti · Accepted solution]

Hi, @Anonymous 

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.

[technolog]

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.

[v-zhangti]

Hi, @Anonymous 

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.

[Anonymous]

Thank you for having a look at it @v-zhangti .
I've added the table