Calculate sum of column values in Power Qwery

[Anonymous]

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

Entite	NbUser	NbUser1
ORANGE/SG/DIG/DIT IDF	18	81
ORANGE/SG/DIG/DIT IDF/EXP/CEXP	16	16
ORANGE/SG/DIG/DIT IDF/EXP	27	43
ORANGE/SG/DIG/DIT IDF/PDI	20	20

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

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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]

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]

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

---------

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