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

[Jiro]

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 activies are:

1. Container Gate In Loading

2. Contained Loaded on Vessel

3. Vessel Departed

4. Container Arrived

However, when creating a new columns I want it to check from the last one (arrived) then back to the beginning (gate in loading). 

Based in below screenshots, it should start checking from the far right and since there is no value it will keep checking to gate in and then return as "gate in loading" in a new column.

Thank you in advance.

[v-cgao-msft · Accepted solution]

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 · Accepted solution]

@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!

[v-cgao-msft · Accepted solution]

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]

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!