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 ho...
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.

     

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