Forum Discussion
custom column to compare standard
Hello, everone.
I use a lot of DAX to handle data and I'm gradually migrating to M.
This time I'm trying to format all clients that start with the first 10 digits of the CNPJ with the same name...
In Dax I do it:
var comCNPJ =
MAXX(
FILTER(ALL('1.4 SQL Clientes'),
LEFT('1.4 SQL Clientes'[CNPJ_CPF], 10) = LEFT(EARLIER('1.4 SQL Clientes'[CNPJ_CPF]), 10)
), '1.4 SQL Clientes'[Nome]
)
RETURN
IF('1.4 SQL Clientes'[CNPJ_CPF] = BLANK(), '1.4 SQL Clientes'[Nome], comCNPJ)
Does anyone have any idea how to do this in power query?
Tank you!
Hi PedroAliança ,
You want the following calculations to be listed in Power Query, right?
Here's the solution in Power Query.
1.Add a custom column to return the first ten characters starting from the left.
2.Add an index column.
3.Group by the [LEFT10] and set up as follows.
4.Expand the All Rows column
5.Add a conditional column to compare.
6.Fill up the column.
7.Sort based on the index column and the [Custom] column is the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
=let fx=(t)=>Text.Start(t??"",10),a=Table.Buffer(#table({"CNP","Nome"},Table.Group(#"1.4 SQL Clientes","CNPJ_CPF",{"n",each {fx([CNPJ_CPF]{0}),List.Max([Nome])}},1,(x,y)=>Value.Compare(fx(x),fx(y)))[n]) in Table.AddColumn(#"1.4 SQL Clientes","Custom",each if [CNPJ_CPF] =null or [CNPJ_CPF]="" then [Nome] else a{[CNP=fx([CNPJ_CPF])]}[Nome])
do i use this in the advanced editor or do i add a new column?
Anyway it is returning the error: Token Comma expected.
Hello, brow!
I need to add a ")" before the in
The formula stayed like this=let
fx=(t)=>Text.Start(t??"",10),a=Table.Buffer(#table({"CNP","Nome"},Table.Group(#"1.4 SQL Clientes","CNPJ_CPF",{"n",each {fx([CNPJ_CPF]{0}),List.Max([Nome])}},1,(x,y)=>Value.Compare(fx(x),fx(y)))[n]))
in
Table.AddColumn(#"1.4 SQL Clientes","Custom",each if [CNPJ_CPF] =null or [CNPJ_CPF]="" then [Nome] else a{[CNP=fx([CNPJ_CPF])]}[Nome])I managed to create the column but returns Error
Can you help me?
Thanks!
Hi PedroAliança ,
You want the following calculations to be listed in Power Query, right?
Here's the solution in Power Query.
1.Add a custom column to return the first ten characters starting from the left.
2.Add an index column.
3.Group by the [LEFT10] and set up as follows.
4.Expand the All Rows column
5.Add a conditional column to compare.
6.Fill up the column.
7.Sort based on the index column and the [Custom] column is the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good Morning!
It worked just fine. Thanks for the class!
Can sameone help me?
I suggest you post a usable example of data (either as text which can be copy/pasted or as a link to an uploaded workbook) as well as an example (could be a screenshot), of the results you expect from that same data. Obscure any confidential information, but ensure the data is representative.
