cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Query editor - Sum of Distinct Count by column

Hello,
I have tables see the attachment, can I create a new column in the table that will contain the sum of the column number?

 Text number RESULT: sum of column number Text1 4,2 7,6 Text2 1,1 7,6 Text3 2,3 7,6

1 ACCEPTED SOLUTION
Community Champion

In the code below I just add a column with a slightly different name.

```let
Zdroj = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Text", type text}, {"number", type number}, {"RESULT: sum of column number", type number}}),
#"Added Custom" = Table.AddColumn(#"Změněný typ", "Sum of column number", each List.Sum(#"Změněný typ"[number]))
in
Specializing in Power Query Formula Language (M)
5 REPLIES 5
Super User

@skopcak

split the colums then sum,  then concatenate the result but i think you might have to do some in DAX too

in power query split the columns into 2

create a calculated column that sums all in dax   (i.e CALCULATE(SUM(field1),ALL(table)) , (i.e CALCULATE(SUM(field2),ALL(table))

in dax join them back together  (concatenate)

might be better solutions out there

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Community Champion

You can simply add a custom column with the formula below, in which "<PreviousStep>" is the name of the previous step in your query:

`= List.Sum(<PreviousStep>[number])`

Otherwise it looks to me like a DAX measure would be more appropriate.

Specializing in Power Query Formula Language (M)
Helper I

You should be able to suggest solutions in code?

CODE:

let
Zdroj = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Text", type text}, {"number", type number}, {"RESULT: sum of column number", type number}})
in
#"Změněný typ"

:CODE

Community Champion

In the code below I just add a column with a slightly different name.

```let
Zdroj = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content],
#"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Text", type text}, {"number", type number}, {"RESULT: sum of column number", type number}}),
#"Added Custom" = Table.AddColumn(#"Změněný typ", "Sum of column number", each List.Sum(#"Změněný typ"[number]))
in
Specializing in Power Query Formula Language (M)
Helper I

Thank you very much, that's exactly what I needed.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors