Fill down with multiple conditions

[Richard_Halsall]

Hi

I have the data as per this query

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdnbTttAGATgV0G5RsLnw6UL4dQ0HFOgCEUmpDTUdajZpLRPX5sUyRLtbDT1VIpKDMzHze5m/7m+7h3c9TZ72/PSVPnEzKvxeFK/PzN5ZcY7uZmu3pjF0+obu4uiGLcfHFfzh+nErN70nx/rr6d3G62nN5vXvTw8Dvadl3+jqyd/lO3VP5w73p+HZTVclCdZv37oOVtOuOU5nl+/uaxfWVHMJ3mTOZybjWyZz4r8tmj+rLL+U17/e4sECHE7QkKEeB0hEUL8jpAYIUFHSIKQsCMkRUjUEZKNsot/InFHyDuEJB0h2whJO0J2AOJ3teL7CGmv+EH9Oipf96iXX/FXv+LuH4ZXxUk2+PvDt+YuMJ3ajATmHjI9jbmPTF9jHiAz0JiHyAw15ntkRhpzgMxYY35AZqIxh8hMNeYRMF1HYx4jU7QPnSBTtA+dIlO0D50hs70PrXV4rUWeIzKUkCNERhLyIyJjCXmByERCXiIylZBX6DOdIyE/IdKVkLnl8iUgby1XMQE5sVzMBOSd5ZomIKeWS5uA/IxIze5zj0jRJ6AvyBR9ApoB0xd9AnoAZnMTiwXmV2R6GrNApq8xvyEz0JglMkONOUdmpDEfkRlrzO/ITDRmhcxUYz4Bs7mJKUyDTNE+tEBmex86b15VvpwWxay8/y9zicz2PpQ1r7WHeD9QbEDHPqPYkI79iWIjOvYXio3ZWOOg2ISOdVFsSsd6lpaIjIXlk0vHwrrJo2NhwUSvMgMrJXqVGVgihZJ9yMBOSXNmG1gxac5sAxsnzZltYAGlObMN6qN8zZltYD2lObMNaqua+0oiMGFb5WlM2Fb5GhO2VYHGhG1VqDFhWxVpTNhWxRoTtlWJxoRtVdoyOzzLUFvV3FcS7liGhZRLx8LOyaNjYa3k07GwOQroWFgOhXQsLIAiOhaWPDEdC4uchI6FZU1Kx6JCxuNXGSxd+FUGixV+lcHyhF9lsCDhVxksQfhVBosOfpXBMoNfZbCw4FcZLCX4VYZ6B59fZbBa4FeZrT1IuVhbQUDG2joAMtY25idjbZN8MtY2rCdjbfN4MtY2cidjbVN1MtY2OCdjbbNxMtY2/iZjbRNuMtY24SZjbRNuMtY24SZjbRNuLnZhm3CTsbYJ93qxN78B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", type text}, {"Contractor__c", type text}, {"Start_Date", type date}, {"Status__c", type text}, {"Full_Status__c", type text}, {"Project__c", type text}, {"Expected Project__c", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start_Date", Order.Ascending}})
in
#"Sorted Rows"

What I am attempting to do is fill down the value in the previous row Project__c column ONLY when the current row Status__c = X and the current row Contractor__c value equals the previous row Contractor__c value

The result should look as per the data in the column Expected Project__c

Can anybody help? Many Thanks

[Papermain]

But what happens when the previous row value is a null value? In your expected result it will show an entry but is that as expected? It does not say so in your logic. 

I have come to this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdnbTttAGATgV0G5RsLnw6UL4dQ0HFOgCEUmpDTUdajZpLRPX5sUyRLtbDT1VIpKDMzHze5m/7m+7h3c9TZ72/PSVPnEzKvxeFK/PzN5ZcY7uZmu3pjF0+obu4uiGLcfHFfzh+nErN70nx/rr6d3G62nN5vXvTw8Dvadl3+jqyd/lO3VP5w73p+HZTVclCdZv37oOVtOuOU5nl+/uaxfWVHMJ3mTOZybjWyZz4r8tmj+rLL+U17/e4sECHE7QkKEeB0hEUL8jpAYIUFHSIKQsCMkRUjUEZKNsot/InFHyDuEJB0h2whJO0J2AOJ3teL7CGmv+EH9Oipf96iXX/FXv+LuH4ZXxUk2+PvDt+YuMJ3ajATmHjI9jbmPTF9jHiAz0JiHyAw15ntkRhpzgMxYY35AZqIxh8hMNeYRMF1HYx4jU7QPnSBTtA+dIlO0D50hs70PrXV4rUWeIzKUkCNERhLyIyJjCXmByERCXiIylZBX6DOdIyE/IdKVkLnl8iUgby1XMQE5sVzMBOSd5ZomIKeWS5uA/IxIze5zj0jRJ6AvyBR9ApoB0xd9AnoAZnMTiwXmV2R6GrNApq8xvyEz0JglMkONOUdmpDEfkRlrzO/ITDRmhcxUYz4Bs7mJKUyDTNE+tEBmex86b15VvpwWxay8/y9zicz2PpQ1r7WHeD9QbEDHPqPYkI79iWIjOvYXio3ZWOOg2ISOdVFsSsd6lpaIjIXlk0vHwrrJo2NhwUSvMgMrJXqVGVgihZJ9yMBOSXNmG1gxac5sAxsnzZltYAGlObMN6qN8zZltYD2lObMNaqua+0oiMGFb5WlM2Fb5GhO2VYHGhG1VqDFhWxVpTNhWxRoTtlWJxoRtVdoyOzzLUFvV3FcS7liGhZRLx8LOyaNjYa3k07GwOQroWFgOhXQsLIAiOhaWPDEdC4uchI6FZU1Kx6JCxuNXGSxd+FUGixV+lcHyhF9lsCDhVxksQfhVBosOfpXBMoNfZbCw4FcZLCX4VYZ6B59fZbBa4FeZrT1IuVhbQUDG2joAMtY25idjbZN8MtY2rCdjbfN4MtY2cidjbVN1MtY2OCdjbbNxMtY2/iZjbRNuMtY24SZjbRNuMtY24SZjbRNuLnZhm3CTsbYJ93qxN78B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", type text}, {"Contractor__c", type text}, {"Start_Date", type date}, {"Status__c", type text}, {"Full_Status__c", type text}, {"Project__c", type text}, {"Expected Project__c", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start_Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index2" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Contractor__c", "Project__c"}, {"Contractor__c.1", "Project__c.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index2", "Custom", each if [Status__c] = "X" and [Contractor__c] = [Contractor__c.1] then [Project__c.1] else [Project__c]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Id", "Contractor__c", "Start_Date", "Status__c", "Full_Status__c", "Project__c", "Expected Project__c", "Custom", "Index", "Index.1", "Contractor__c.1", "Project__c.1"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Expected results equals custom column?", each [Expected Project__c]=[Custom])
in
    #"Added Custom1"

[Richard_Halsall]

@Papermain Thanks for taking the time to help to resolve this issue, if I can provide further context to assist.

The data is replicating a shift pattern for a project (Project__c) where a contractor (Contractor__c) works for 2 weeks (Status__c = L) and then does not work for 2 weeks (Status__c = X) 

Where the Status__c = X I need to populate the Project__c field with the value from where the Status__c = L

So in the data for Contractor__c = a024H00000nrNunQAE

They work, Status__c = L, from 31/05/2023 to 13/06/2023 on Project__c = a034H00001HJ5YlQAL

They do not work, Status__c = X, 14/06/2023 to 27/06/2023 but I need Project__c to show a034H00001HJ5YlQAL

i.e. they are allocated to the same project but not working

Does that make more sense

Thanks

Richard