Apply condition in Power Query (Filter earliest date based on each Consol ID)

[NickProp28]

Dear Community,

I would like to apply some condition when do the data cleansing in Power Query.

**Only need one date record for each Consol ID**

ETD (DD/MM/YYYY)

Condition as below: 

i.) Date with 'Null' shouldnt be choosen as earliest date when there have available date for that Consol ID.

For example:  C001 with date '1/1/2021' should be selected as earliest date, but C002 doesnt have other available date. In this case, 'Null' can be selected.

ii.) If the Consol ID have few transport mode (SEA or AIR ,RAI,ROA), ONLY priorities on the earliest date of 'SEA' or 'AIR' transport mode.

For example: In C003, even the earliest date is 1/1/2021, but we only focus on the date with transport mode 'SEA' or 'AIR'. So earliest date for C003 is 1/3/2021, while C004 is 1/6/2021.

iii.) If there is only one trasport mode (RAI or ROA), the earliest date will be selected.

For example: '1/1/2021' for C005 and C006. 

Here attached with the pbix: https://ufile.io/twdwgzh8

Thanks for your attention.

Any assitance you can provide would be greatly appreciated!

[v-xiaotang · Accepted solution]

Hi @NickProp28 

try this

Column = 
VAR _t =
    CALCULATE (
        COUNT ( Consol_Date[ConsolID] ),
        FILTER (
            ALLEXCEPT ( Consol_Date, Consol_Date[ConsolID] ),
            Consol_Date[Transport] = "SEA"
                || Consol_Date[Transport] = "AIR"
        )
    )
RETURN
    IF (
        _t >= 1,
        CALCULATE (
            MIN ( Consol_Date[ETD] ),
            FILTER (
                ALLEXCEPT ( Consol_Date, Consol_Date[ConsolID] ),
                Consol_Date[Transport] = "SEA"
                    || Consol_Date[Transport] = "AIR"
            )
        ),
        CALCULATE (
            MIN ( Consol_Date[ETD] ),
            ALLEXCEPT ( Consol_Date, Consol_Date[ConsolID] )
        )
    )

result

Best Regards,

Community Support Team _Tang

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

[amitchandak]

@NickProp28 , Try a new column like

new column =
var _min = minx(filter(Table, [Consol ID] =earlier([Consol ID]) ),[Date])
var _count = countx(filter(Table, [Consol ID] =earlier([Consol ID]) && not [transport mode] in {"SEA","AIR"}),[Date])+0
var _min2 = countx(filter(Table, [Consol ID] =earlier([Consol ID]) && [transport mode] in {"SEA","AIR"}),[Date])
return
if(_count >=1, _min2, _min)

[NickProp28]

Dear @amitchandak ,

Thanks for your prompt response. 

But after I applied the code you provided. 

C003 until C005 retrieved null and invalid date.

C004 and C005, if there's only one transport mode (No matter is ROA/ROI) . The earliest date will be selected. 

Kindly advice.