Forum Discussion

KuntalSingh's avatar
KuntalSingh
Helper V
2 years ago
Solved

Need help on If condition

Need to apply excel formual in power query  =IF(AND(LEN(M2)-LEN(M3)<=2,(LEN(M2)-LEN(M3)<>0),ABS(R2)=ABS(R3),ISERROR(SEARCH(M2,M3,1))=FALSE),TRUE,FALSE) M is Reference column and R is Amountindoccur...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solutions lbendlin  and ronrsnfld offered and i want to offer some more information for user to refet to.

    hello KuntalSingh , you can refer to the following code in advanced editor in power query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZS7TgMxEEX/Zesksr0eP0q0oaAACQqEFOX/fwOPKSDJPSvSZc4eW7oz48tlCeNXWnp5Ww7LsRZr/WTL9XAL7uvVP47NSvgtRfNiajHZ3+KTrH48f466Km+vCM501EAbW9s8EsW9C/fheffcf1CA7zvoiw59vK7VNffjT2Pj+Nsf6zflHtYQa/Jyyr2d0h2Y8Uu0krOyk8nJ7Bg5xk4hp7BTyansNHIaO52cDo5l044DdIruqQNwSii6Pw7Y0f1xwI7ujwN2dH8csKP744AdnbUDdGoApwZ2IjmRHd1TB+zoPXXADs0B7ulANAe4pwPRHOCeDkRzQHuaR0XeMwE78p4J2JHzNgE7ct4mQMdk1hOwQxkYZ2CUgXEGRhkYZ1DkjE7AjpzRCdih3OitckS50Vs1kN7TCdihDGhPHVEGtKc5x/Xm3bl+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Amountindoccurr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
a=[Index],
b=Table.SelectRows(#"Added Index",each [Index]=a+1),
c=b[Reference]{0},
d=b[Amountindoccurr]{0},
e= if Text.Length([Reference])-Text.Length(c)<=2 and Text.Length([Reference])-Text.Length(c)<>0 and Number.Abs([Amountindoccurr])=Number.Abs(d) and Text.Contains(c,[Reference]) then true else false
in try e otherwise null)
in
    #"Added Custom"

    Output

     If the solutions lbendlin , ronrsnfld  and i offered help you , you can consider to accept them as solutions so that more user can refer to.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

Here is one method.

Read the notes in the M Code to understand the algorithm

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Amountindoccurr", type number}}),
    
//Add shifted columns to easily access current and subsequent rows
    #"Add Shifted Columns" = Table.FromColumns(
        Table.ToColumns(#"Changed Type") &
        {List.RemoveFirstN(#"Changed Type"[Reference],1) & {null}} &
        {List.RemoveFirstN(#"Changed Type"[Amountindoccurr],1) & {null}},
        type table[Reference=text,Amountindoccurr=number, Shifted Reference=text, Shifted Amountindoccurr=number]),

//Apply your logical equation
    #"Add T/F" = Table.AddColumn(#"Add Shifted Columns","T/F", each 
        (Text.Length([Reference]) - Text.Length([Shifted Reference]) <=2) and 
            (Text.Length([Reference]) - Text.Length([Shifted Reference]) <> 0) and 
            (Number.Abs([Amountindoccurr]) = Number.Abs([Shifted Amountindoccurr])) and 
            (Text.Contains([Shifted Reference],[Reference], Comparer.OrdinalIgnoreCase)),
         type logical),

//Remove the shifted columns
    #"Removed Columns" = Table.RemoveColumns(#"Add T/F",{"Shifted Reference", "Shifted Amountindoccurr"})
in
    #"Removed Columns"

 

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Ah, if only there were a Table.Zip function 🙂

     

    You could use List.Skip for some premature optimization.