Finding the next row of information based on parameters

[Anonymous]

Hi all! 

I have a the table below, and I would like to get the following table.. basically I want to be able to look up the "crews" next different "shift ID" and diplay the corresponding "start date", "crew", "shift ID" and "client"

Orginal table:

Desired Table:

And if there is nothing avalible to happen next, I would like to display some writing like "No Next Shift ID" and "No Next Start Date" and "No Next Client"

Any help would be greatly appricated! 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Here is the codes for transformations in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdI9C8IwEAbg/9JZSHpJmrvRj0EcXBwcxKFDQaG2UATx35srtjltNYSQF/IMyZucTlmhwCrQoLMFZzfkZZho0Zqwrq51nZ0XE1x8YyuxV/mwL+IqTEOGelp29ymFBMVIx7hmGiTnbXuruqmllM2dOO6YN2Fasv0hdu2lmcNFAgOI24EszWEY79Kec9okNQlNH9XpMMK6rx73GWz0X4xqvKGI3B14NJ4vWzZTWfyXOUQaM9dm0BIf4FDV16ac4ybNSXCSzZHD/lGOw+f8xKATOH51mbk4i46In/sH9r/x+QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, Date = _t, Crew = _t, #"Shift ID" = _t, Client = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"Date", type date}, {"Crew", type text}, {"Shift ID", Int64.Type}, {"Client", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type","Next Start Date",each let 
x=[Start Date],y=[Crew]
in
List.Min(Table.Column(
    Table.SelectRows(#"Changed Type",each [Start Date]>x and [Crew]=y),
    "Start Date"
)
)

),
    Custom2 = Table.AddColumn(Custom1,"Next Crew",each [Crew]),
    Custom3 = Table.AddColumn(Custom2,"Next",each let 
x=[Next Start Date],y=[Next Crew]
in
List.First(
    Table.Column(
        Table.SelectRows(Custom2,each [Start Date]=x and [Crew]=y),
        "Shift ID"
    )
)

),
    Custom4 = Table.AddColumn(Custom2,"Next",each let 
x=[Next Start Date],y=[Next Crew]
in
List.First(
    Table.Column(
        Table.SelectRows(Custom2,each [Start Date]=x and [Crew]=y),
        "Client"
    )
)

)
in
    Custom4

Result:

Best Regards

Allan

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

[Anonymous]

forse  questo fa al caso tuo:

...
    cp = Table.AddColumn(yourTab, "next",  (r)=> try Table.SelectRows(yourTab, each _[crew]=r[crew] and _[date]>r[date]){0} otherwise null),
    te = Table.ExpandRecordColumn(cp, "next", {"date", "crew", "id", "client"}, {"next.date", "next.crew", "next.id", "next.client"})
in
    te

you could input the line of code a via Avanced Editor as above illustrated or via User Interface, Adding a custom column like is done in the folliwing picture:

and finally expanding the record column obtained from previous step

[Rich_P]

I sweated through a problem just like this for TOO MANY hours last night. Got my solution to work by brute force, but it was terribly slow. So glad I found your 2-line solution. Works like a charm! Thanks so much!

[Mariusz]

Hi @Anonymous 

Sure, can you paste a sample table here, so we can use it to create a solution?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hey @Mariusz 

What do you mean sample table?