Forum Discussion

GordyM1's avatar
GordyM1
Regular Visitor
4 years ago
Solved

If AND formula help

Hi,

 

I have some data where I want to identify a break in the sequence of the data. I show the Excel formula that does this but I would like to have this calculation in Power Query but not sure how to write it?

 

In this instance I have identified that for this Item that 2022 is missing, it goes from 2021 - 2022 when the MSQ is greater than zero.

 

ItemIDItem NumberFYMSQColumn1 
540COS-RAD77302021700  
540COS-RAD77302021700  
540COS-RAD77302021700  
540COS-RAD77302021525  
540COS-RAD77302023350xIFERROR(IF(AND(A6=A5,D5>0,(C6-C5)>1),"x",""),"")
540COS-RAD77302023350  
540COS-RAD77302023350  
540COS-RAD77302023350  
540COS-RAD77302023350  
540COS-RAD77302023350  
540COS-RAD77302023350  

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi GordyM1 ,

    According to your description, here's my solution.

    1.Create a index column.

    2.Create a custom column.

    Custom = if [Index]=0 then "" else if [ItemID]=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[ItemID]{0} and Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[MSQ]{0}>0 and [FY]-Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[FY]{0}>1 then "x" else""

    Or you can create a calculated column in DAX.

    Column =
VAR _T =
    FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
RETURN
    IF (
        'Table'[ItemID] = MAXX ( _T, 'Table'[ItemID] )
            && MAXX ( _T, 'Table'[MSQ] ) > 0
            && 'Table'[FY] - MAXX ( _T, 'Table'[FY] ) > 1,
        "x",
        ""
    )

    Both methods can get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

3 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi GordyM1 ,

    According to your description, here's my solution.

    1.Create a index column.

    2.Create a custom column.

    Custom = if [Index]=0 then "" else if [ItemID]=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[ItemID]{0} and Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[MSQ]{0}>0 and [FY]-Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]-1)[FY]{0}>1 then "x" else""

    Or you can create a calculated column in DAX.

    Column =
VAR _T =
    FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
RETURN
    IF (
        'Table'[ItemID] = MAXX ( _T, 'Table'[ItemID] )
            && MAXX ( _T, 'Table'[MSQ] ) > 0
            && 'Table'[FY] - MAXX ( _T, 'Table'[FY] ) > 1,
        "x",
        ""
    )

    Both methods can get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    This is possible to do in Power Query, but what do you plan to do with it once you have it? It might be better to just write a DAX measure and/or create a visual with your existing data that shows the missing year for that item. In the example below, I connected your data table to a simple date table with year (for the column values) and did a simple count of Items with the option of "Show Items With No Data" selected.

     

    Pat

     

    • GordyM1's avatar
      GordyM1
      Regular Visitor
      4 years ago

      Hi Pat,

       

      Thanks for the reply. Once I have identified the Item and Row where the gap is I go to my source data and make a manual entry so that there is no gap in the sequence. I currently do this in the excel table but wanted it done in Power Query (I have about 50K lines of data in the table). Not sure how to do a Dax measure?

       

      Gordon