Need help on If condition

[KuntalSingh]

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 Amountindoccurr column

Data is

Reference	Amountindoccurr
0000682IN	-76589.5
0000682IN	76589.5
00007	-18560
0000715	-28125
0000715A	-28125
0000715REV	28125
0000715REVCM	28125
0000715REVCMD	-28125
0000715REVCMDC	28125
0000715REVCMDCCM	-28125
0000715REVCMDCCM	28125
0000715REVCMDCCM	28125
0000715REVCMDCDM	-28125
0000715REVCMDCDM	-28125
0000715REVCMDCDM	28125
0000715REVCMDQ	28125
0000715REVCMDQX	-28125
0000715REVDM	-28125
0087349-IN	-10089
0087349-IN	10089
0089030172	12498.2
0089030172D	-12498.2
0089030173	12498.2
0089030173D	-12498.2
0089030174	12498.2
0089030174D	-12498.2
0089030175	12498.2
0089030175D	-12498.2
0089030176	12498.2
0089030176D	-12498.2
0089030177	12498.2
0089030177D	-12498.2
0089030178	12498.2
0089030178D	-12498.2
0089030179	12498.2
0089030179D	-12498.2
0089035459	12498.2
0089035459D	-12498.2
0089035462	12498.2
0089035462D	-12498.2
0089036064	12498.2
0089036064D	-12498.2
0089036065	12498.2
0089036065D	-12498.2
0089036066	12498.2
0089036066D	-12498.2
0089036067	12498.2
0089036067D	-12498.2
0089036068	12498.2
0089036068D	-12498.2
0089036069	12498.2
0089036069D	-12498.2
0089036070	12498.2
0089036070D	-12498.2
0089036071	12498.2
0089036071D	-12498.2
0089036072	12498.2
0089036072D	-12498.2
0089036073	12498.2
0089036073D	-12498.2
0089036074	12498.2
0089036074D	-12498.2
0089036075	12498.2
0089036075D	-12498.2
0089036076	12498.2
0089036076D	-12498.2
0089036077	12498.2
0089036077D	-12498.2
0089041246	12498.2
0089041246D	-12498.2
0089041247	12498.2
0089041247D	-12498.2
0089041248	12498.2
0089041248D	-12498.2
0089041249	12498.2
0089041249D	-12498.2
0089041255	12498.2
0089041255D	-12498.2
0089041256	12498.2
0089041256D	-12498.2
0089041257	12498.2
0089041257D	-12498.2
0089041258	12498.2
0089041258D	-12498.2
0089041263	12498.2
0089041263D	-12498.2
0089041264	12498.2
0089041264D	-12498.2
0089041265	12498.2
0089041265D	-12498.2
0089041266	12498.2
0089041266D	-12498.2
0089041272	12498.2
0089041272D	-12498.2
0089041273	12498.2
0089041273D	-12498.2
0089041274	12498.2
0089041274D	-12498.2
0089044131	12498.2

[Anonymous · Accepted solution]

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.

[KuntalSingh]

Thanks for your efforts I accept this as solution, However it's take to much time to load the data 

[ronrsnfld]

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]

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

You could use List.Skip for some premature optimization.

[lbendlin]

What is the expected result for the first row?

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,{{"Amountindoccurr", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Check", each 
if Text.Length(#"Added Index"{[Index]-1}[Reference])-Text.Length([Reference])<=2 
and Text.Length(#"Added Index"{[Index]-1}[Reference])<>Text.Length([Reference])
and Number.Abs(#"Added Index"{[Index]-1}[Amountindoccurr])=Number.Abs([Amountindoccurr])
and Text.StartsWith([Reference],#"Added Index"{[Index]-1}[Reference])
then true else false)
in
    #"Added Custom"