Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]))
Hi Evelyn,
yes, it was.
and I accepted proposed solution already .
Thx
Hi @JacquesG ,
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
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]))
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
(b) => Text.Start(b[Entite], Text.Length(a[Entite])) = a[Entite])[Entite]))
---------
or (b) => Text.StartsWith(b[Entite],a[Entite]))[Entite]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.