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

Data is

ReferenceAmountindoccurr
0000682IN-76589.5
0000682IN76589.5
00007-18560
0000715-28125
0000715A-28125
0000715REV28125
0000715REVCM28125
0000715REVCMD-28125
0000715REVCMDC28125
0000715REVCMDCCM-28125
0000715REVCMDCCM28125
0000715REVCMDCCM28125
0000715REVCMDCDM-28125
0000715REVCMDCDM-28125
0000715REVCMDCDM28125
0000715REVCMDQ28125
0000715REVCMDQX-28125
0000715REVDM-28125
0087349-IN-10089
0087349-IN10089
008903017212498.2
0089030172D-12498.2
008903017312498.2
0089030173D-12498.2
008903017412498.2
0089030174D-12498.2
008903017512498.2
0089030175D-12498.2
008903017612498.2
0089030176D-12498.2
008903017712498.2
0089030177D-12498.2
008903017812498.2
0089030178D-12498.2
008903017912498.2
0089030179D-12498.2
008903545912498.2
0089035459D-12498.2
008903546212498.2
0089035462D-12498.2
008903606412498.2
0089036064D-12498.2
008903606512498.2
0089036065D-12498.2
008903606612498.2
0089036066D-12498.2
008903606712498.2
0089036067D-12498.2
008903606812498.2
0089036068D-12498.2
008903606912498.2
0089036069D-12498.2
008903607012498.2
0089036070D-12498.2
008903607112498.2
0089036071D-12498.2
008903607212498.2
0089036072D-12498.2
008903607312498.2
0089036073D-12498.2
008903607412498.2
0089036074D-12498.2
008903607512498.2
0089036075D-12498.2
008903607612498.2
0089036076D-12498.2
008903607712498.2
0089036077D-12498.2
008904124612498.2
0089041246D-12498.2
008904124712498.2
0089041247D-12498.2
008904124812498.2
0089041248D-12498.2
008904124912498.2
0089041249D-12498.2
008904125512498.2
0089041255D-12498.2
008904125612498.2
0089041256D-12498.2
008904125712498.2
0089041257D-12498.2
008904125812498.2
0089041258D-12498.2
008904126312498.2
0089041263D-12498.2
008904126412498.2
0089041264D-12498.2
008904126512498.2
0089041265D-12498.2
008904126612498.2
0089041266D-12498.2
008904127212498.2
0089041272D-12498.2
008904127312498.2
0089041273D-12498.2
008904127412498.2
0089041274D-12498.2
008904413112498.2
  • 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.

5 Replies

  • 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

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

       

      You could use List.Skip for some premature optimization.

  • 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"
  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      KuntalSingh
      Helper V

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