Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Community,
it seems so simple, yet I havent found an adequate solution.
Initial Situation:
There is a table of multiple columns. Some of which are named button_1, button_2 etc. Sometimes there are values in those columns.
Sought Output as a new column:
The function should sum up the values of multiple columns.
In that example it is a simple sum function from button_1 to button_4.
How can this be achieved?
Solved! Go to Solution.
In case you have 200 columns to sum....select the 1st column>>press the shift key>>>scroll to the last (200th column) and select it.
This way all columns will be selected
Then you can add a new column summing all 200 columns......... from the "ADD COLUMN" tab >>>"STATISTICS">>>"SUM"...... as I mentioned above
Hi Raymond,
To achieve your requirement, there’re two solutions as follow:
1.You can create a new column [Output], and use the Dax formula below:
Output = Table1[button_1] + Table1[button_2] + Table1[button_3] + Table1[button_4]
2.In edit query, first convert all data in your table to number, then follow steps below:
You can also add M code as below in Advanced Editor:
#"Added Custom" = Table.AddColumn(#"Previous Step Name", "Output", each [button_1] + [button_2] + [button_3] + [button_4])
Please refer to PBIX file: https://www.dropbox.com/s/2q25lxuug3c9y7c/For%20raymond.pbix?dl=0
Regards,
Jimmy Tao
Hi,
I have applied the second approach for taking out the sum through Power Query.
But I am still getting an error.
And when i clicked on the error it shows this :
Expression.Error: We cannot apply operator + to types Number and Text.
Details:
Operator=+
Left=8
Right=0
There are nine columns which i want to add and every column has numeric value and no blank cell.
= Table.AddColumn(#"Removed Columns1", "Facilitation Skills", each [form.Following_session_flow] + [form.Relevant_and_effective_icebreaker] + [form.Using_concrete_familiar_language_with_relatable_examples] + [form.Voice_Clarity_Volume_Modulation] + [form.Displaying_effective_nonverbal_communication] + [form.Handling_differences_of_opinion_and_conflicts_group_dynamics_during_the_session_with_participant_and_the_environment_surrounding_the_session] + [form.Ask_questions_appropriately_to_allow_participant_to_think_respond_to_participants_questions] + [form.Time_management] + [form.Closing_session_with_a_quick_recap_of_key_learning])
Hi,
Atleast one of the columns is formatted as text/alphanumeric. Click on any step before the summation step, select all numeric columns > right click > Change Type > Decimal.
Hope this helps.
@v-yuta-msftand @Zubair_Muhammad thank you so much. This was easy - but isnt there another way to maybe define a range of columns where the values need to be summed up or do I really have to name every single column.
Suppose I have a table with 200 columns and I want to summarize all of them quickly.
In case you have 200 columns to sum....select the 1st column>>press the shift key>>>scroll to the last (200th column) and select it.
This way all columns will be selected
Then you can add a new column summing all 200 columns......... from the "ADD COLUMN" tab >>>"STATISTICS">>>"SUM"...... as I mentioned above
It worked. Thanks.
HI @raymond
Go to Query Editor>>> Select the Button Columns>>>Go to "Add Column" Tab>>>Statistics>>>Sum