Forum Discussion

NickProp28's avatar
NickProp28
Post Partisan
5 years ago
Solved

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

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!

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      NickProp28
      Post Partisan
      5 years ago

      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.

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    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.