Forum Discussion

PedroAliança's avatar
PedroAliança
Frequent Visitor
3 years ago
Solved

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 na...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

=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's avatar
    PedroAliança
    Frequent Visitor
    3 years ago

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

    Anyway it is returning the error: Token Comma expected.

  • PedroAliança's avatar
    PedroAliança
    Frequent Visitor
    3 years ago

    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!