Forum Discussion
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...
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.
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"