custom column to compare standard

[PedroAliança]

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:

Spoiler

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!

[v-stephen-msft · Accepted solution]

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.

[PedroAliança]

Good Morning!

It worked just fine. Thanks for the class!

[PedroAliança]

Can sameone help me?

[ronrsnfld]

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.

[wdx223_Daniel]

=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])

[PedroAliança]

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!

[PedroAliança]

do i use this in the advanced editor or do i add a new column?

Anyway it is returning the error: Token Comma expected.