Power Query

[Anonymous]

Hi,

My original table looks like this:

I then created some steps which finds the start job date and end job date based Job Name column. However, it also includes the "Cancelled" Booking Types. I want to exclude rows with the "Cancelled" booking type.

The table that I have right now looks like this after the steps:

The correct job duration for Job A should be 1/10/2022 to 28/10/2022.

The correct job duration for Job B should be 2/11/2022 to 21/11/2022

Does anyone know what I am missing in my steps? I'm quite unfamiliar with the Power Query so I would appreciate all the help i can get. I'll upload the file here. Thanks Alot.

https://drive.google.com/drive/folders/1PFxkYhXnHubuT3N1hQ2vJiNikYV4DibC?usp=share_link

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

If you want to transform date in Power Query, please crete two columns:

Start  job duration = List.Max(Table.SelectRows(#"Promoted Headers",(x)=>x[Job Name]=[Job Name] and x[Booking Type]<>"Cancelled")[Start Date])

End job duration = List.Max(Table.SelectRows(#"Promoted Headers",(x)=>x[Job Name]=[Job Name] and x[Booking Type]<>"Cancelled")[End Date])

Result you want:

M code for your reference:

let
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Staff Name", type text}, {"Job Name", type text}, {"Start Date", type date}, {"End Date", type date}, {"Booking Type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Start  job duration", each List.Max(Table.SelectRows(#"Promoted Headers",(x)=>x[Job Name]=[Job Name] and x[Booking Type]<>"Cancelled")[Start Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "End job duration", each List.Max(Table.SelectRows(#"Promoted Headers",(x)=>x[Job Name]=[Job Name] and x[Booking Type]<>"Cancelled")[End Date]))
in
    #"Added Custom1"

Best regards,

Yadong Fang

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

[v-yadongf-msft]

Hi @Anonymous ,

I think you can transform date in Power BI.

Please create two columns:

Start job duration =
MINX (
    FILTER (
        'Table',
        'Table'[Booking Type] <> "Cancelled"
            && 'Table'[Job Name] = EARLIER ( 'Table'[Job Name] )
    ),
    [Start Date]
)


End job duration =
MAXX (
    FILTER (
        'Table',
        'Table'[Booking Type] <> "Cancelled"
            && 'Table'[Job Name] = EARLIER ( 'Table'[Job Name] )
    ),
    [End Date]
)

The result you want:

Best regards,

Yadong Fang

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