Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I cannot figure out how to summerize all values from one column (calculate the total) and add them to a new column ("total") using Power Query. In Excel I am used to simply use the "=SUM" formula. The end goal is to use the total value to be able to create another column ("Part of total") where I can calculate the first values ("value") as part of total value. Hard to describe with words so please see below screenshot of the problem I am trying to explain.
Thanks in advance for any help give and please excuse me if this question has already been brought up. But to be honest I have search for an answer but only find solutions to more advances summerizing problems.
Solved! Go to Solution.
HI @DBricklin1979 - Sorry my mistake, I was thinking of the syntax for a Group By function. You need to replace the "_" with the table name from the previous step. It would look like this:
= Table.AddColumn(#"Andrad typ", "Anpassad", each List.Sum( #"Andres typ"[Value] ) )
HI @DBricklin1979 - Sorry my mistake, I was thinking of the syntax for a Group By function. You need to replace the "_" with the table name from the previous step. It would look like this:
= Table.AddColumn(#"Andrad typ", "Anpassad", each List.Sum( #"Andres typ"[Value] ) )
Perfect! It worked! Thanks @Daryl-Lynch-Bzy !
One question though; why do you write: # ?
The # is system break flag, it tell Power Query that the following is not "Text" but a reference to an Object called #"Text".
If your previous step had a simple name like Source, you don't use "". Power Query knows that your reference to the Source Step instead of "Source" text. But when the object has a complex name "Two Words", you need to add #"" to reference the object.
Me personally, I don't like one work step names for steps or queries (e.g. ThisStepDoesSomething). I prefer spaces (i.e. #"This Step Does Something"). The advantage is when using intellisence. If I start type with #" I will immediately see a list of items to scroll down and select. If you start type This it will still works, but some times the word is too similar to Power Query function, so the list contains other items.
Hi @DBricklin1979 - you need to use the following formula in a Custom Column in Power Query.
List.Sum( _[Value] )
Note the key is the _. This will load the entire table column. Where have [Value] part only will include the current row value.
However, it may be better to leave this type of calculation until DAX/Data Model stage as it will slow the import of data.
Thanks @Daryl-Lynch-Bzy ! Unfortunantly it did not work for me. I get the error below:
It says: "Error: Cannot convert value 10 to the type list"