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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate sum of column values in Power Qwery

Hi,

I need to implement a function in Power Query M to add a column to a table, that will sum column x values , if one condition is true: in following example ,

NbUser1 = sum of NbUser (all rows) when begining of "Entite" (= length of "Entite" of evaluated line )  is equal to "Entite" of evaluated line 

in this example I just put an extract of the file where all lines start with searched string ORANGE/SG/DIG/DIT IDF

EntiteNbUserNbUser1
ORANGE/SG/DIG/DIT IDF1881
ORANGE/SG/DIG/DIT IDF/EXP/CEXP1616
ORANGE/SG/DIG/DIT IDF/EXP2743
ORANGE/SG/DIG/DIT IDF/PDI2020

 

I tried following code (sum values of column after selecting rows that start like evaluated row) but it returns an error:

let
Source = (data as table) =>
let
#"Colonne NbUser" =
 Table.AddColumn(
 data,
 "NBUser_entite",
                      //(a) is a parameter for function, which equals current record
 (a)=> List.Sum(
 Table.SelectRows(
    data,          //(b) equals whole table1. This function returns table filtered by given criteria
    (b) => Text.Start(b[Entite], Text.Length(a[Entite])) = a[Entite]))[Entite])

in
#"Colonne NbUser"
in
Source

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Daniel,
sorry for this reply. my correction was still not correct. 
it is ok with: 
(b) => Text.Start(b[Entite], Text.Length(a[Entite])) = a[Entite])[NbUser]))
or
(b) => Text.StartsWith(b[Entite],a[Entite]))[NbUser]))

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi Evelyn,
yes, it was. 
and  I accepted proposed solution already .
Thx

Anonymous
Not applicable

Hi @Anonymous ,

 

Based on this

it is ok with: 
(b) => Text.Start(b[Entite], Text.Length(a[Entite])) = a[Entite])[NbUser]))
or
(b) => Text.StartsWith(b[Entite],a[Entite]))[NbUser]))

 

So has your issue been resolved?

 

Best Regards,
Eyelyn Qin

 

Anonymous
Not applicable

Hi Daniel,
sorry for this reply. my correction was still not correct. 
it is ok with: 
(b) => Text.Start(b[Entite], Text.Length(a[Entite])) = a[Entite])[NbUser]))
or
(b) => Text.StartsWith(b[Entite],a[Entite]))[NbUser]))

Anonymous
Not applicable

Hi Daniel
thx for your reply
I checked I had braket issue.
I also tried your proposal, I still have an error :
An error occurred in the ‘AddColumn_NbUser-entite (2)’ query. Expression.Error: We cannot apply operator - to types Text and Text.
Details:
Operator=-
Left=ORANGE/SG/DIG/DIT IDF/EXP/CEXP
Right=ORANGE/SG/DIG/DIT IDF/EXP/CEXP

wdx223_Daniel
Super User
Super User

 (b) => Text.Start(b[Entite], Text.Length(a[Entite])) = a[Entite])[Entite]))

---------

or  (b) => Text.StartsWith(b[Entite],a[Entite]))[Entite]))

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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