Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
skopcak
Helper I
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?

 

 

TextnumberRESULT: sum of column number
Text14,27,6
Text21,17,6
Text32,37,6

 

thank you answer

1 ACCEPTED SOLUTION

Strange that you already have the column in your input table.

 

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
    #"Added Custom"
Specializing in Power Query Formula Language (M)

View solution in original post

5 REPLIES 5
vanessafvg
Super User
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!




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)

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

 

thank you for answer

Strange that you already have the column in your input table.

 

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
    #"Added Custom"
Specializing in Power Query Formula Language (M)

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.