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 everyone. Please give me some help.
Let's say I have a Table: [table]
I'd like to add a column whose value is the sum over all this table's columns except for column A and B.
The column names changes every time the table is loaded from its source except for column A and B.
What I am doing now is:
qlist = Table.ColumnNames([table]),
openCAPAlist = List.RemoveItems(qlist, {"A","B"}),
#"Added Custom" = Table.AddColumn([table], "#added column", each List.Sum(Record.ToList(Record.SelectFields(_, openCAPAlist)))),
This works. However when openCAPAlist is big, it takes too long to execute somehow, which is strange given the table has only 19 columns and 74 rows.
Is there any better way to do it?
Solved! Go to Solution.
I solved it by using List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference()))).
Thanks.
Hi,
Just thinking aloud - how about selecting the 2 columns (A and B), right clicking and "unpivoting other columns". Now we can write a DAX measure in the visual to get your desired result.
Hi Ashish, appreciate your reply. I am looking to save the summation into a new columns in the [table]. So Is there a way to do what you said in Query M? Also if so, Can you going into a bit detail about how to go from the unpivoted table to the sum of the rest of the columns? To my understanding, after unpivot, I will be left expanded column A and column B and an extra column with all other column names as this new column's entries' value.
Hi,
Could you share some data and show the expected result?
I solved it by using List.Sum(Record.FieldValues(Record.SelectFields(_, List.Difference()))).
Thanks.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |