If AND formula help

[GordyM1]

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.

ItemID	Item Number	FY	MSQ	Column1
540	COS-RAD7730	2021	700
540	COS-RAD7730	2021	700
540	COS-RAD7730	2021	700
540	COS-RAD7730	2021	525
540	COS-RAD7730	2023	350	x	IFERROR(IF(AND(A6=A5,D5>0,(C6-C5)>1),"x",""),"")
540	COS-RAD7730	2023	350
540	COS-RAD7730	2023	350
540	COS-RAD7730	2023	350
540	COS-RAD7730	2023	350
540	COS-RAD7730	2023	350
540	COS-RAD7730	2023	350

[v-yanjiang-msft · Accepted solution]

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]

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]

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