Forum Discussion

Jiro's avatar
Jiro
Frequent Visitor
2 years ago
Solved

How to let query pick the latest data from multiple columns?

Hi everyone, I am kind of new here so would really appreciate your help. How do we create a new column and pick the latest status out of multiple of existing columns?   The sequence of the activ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Jiro ,

    I'm assuming this is a PowerQuery question, Please add a custom column:

    if not List.IsEmpty(List.RemoveNulls({[Container Arrived]})) then "Arrived"
else if not List.IsEmpty(List.RemoveNulls({[Vessel Departed]})) then "Departed"
else if not List.IsEmpty(List.RemoveNulls({[Contained Loaded on Vessel]})) then "Loaded on Vessel"
else if not List.IsEmpty(List.RemoveNulls({[Container Gate In Loading]})) then "Gate In Loading" 
else null



    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • m_dekorte's avatar
    m_dekorte
    2 years ago

    Jiro 

    Seems I misread your requirement. you could do something like this:

    let
    Source = Table.FromRows(
        {
            {#date(2023,9,15), null, null, null},
            {#date(2023,9,15), #date(2023,9,17), null, null},
            {null, #date(2023,9,15), null, null},
            {#date(2023,9,15), null, #date(2023,9,18), null},
            {null, null, null, #date(2023,9,19)}
        },
        type table[Loading=date, Vessel=date, Departed=date, Arrived=date]
    ),
    AddCustom = Table.AddColumn(Source, "Custom", each 
        Record.FieldNames(_){
            List.PositionOf(
                Record.FieldValues(_), 
                [Arrived] ?? [Departed] ?? [Vessel] ?? [Loading], 
                Occurrence.Last 
            )
        }
    )
in
    AddCustom

     

    to obtain this result

     

    I hope this is helplful

    Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!

m_dekorte's avatar
m_dekorte
Resident Rockstar
2 years ago

There are several way to achieve this, the most simple would be applying coalesce (dubble question mark), this will return the first non-null value - if all values are null a null is returned.

 

To illustrate, coalesce will look like this, you insert the coalesce operator between each field selection: 

[Col4] ?? [Col3] ?? [Col2] ?? [Col1]

 

 

Ps. Please mark this answer as solution when it helped you to resolve your question, thanks!