[Power Query] How to fill missing data from above rows based on condition same Date and Same ID

[Anonymous]

Hello Community,

I have query from SQL server that collecting log from gps the data will update every minute.

the problem is on column D_Name (Driver Name)  some rows  data  are blanks  and i would like to fill data ref from earlier rows 

based on same Truck ID and Same Date  (Due to Driver might change from day to day on the same truck)

how to  fill the correct driver name by Power Query Editor 

here are sample file

Sample file 

example photo :

Please Advice

Best Regards

JJ

[Ahmedx]

pls try this

let
    Source = Excel.Workbook(File.Contents("C:\Users\User\Desktop\SAMPLE.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"V_ID", Int64.Type}, {"V_LICENSE_NO", type text}, {"VLCTL_SMARTCARD_D_CODE", Int64.Type}, {"D_NAME", type text}, {"VLCTL_GPS_DATE", type date}, {"time", type time}, {"VLCTL_ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"V_ID", "VLCTL_GPS_DATE"}, {{"Count", 
(x) => Table.FillDown(
          Table.TransformColumns(x,{"D_NAME",(y)=> if y ="" then null else y}),
    {"D_NAME"})
}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"V_ID", "V_LICENSE_NO", "VLCTL_SMARTCARD_D_CODE", "D_NAME", "VLCTL_GPS_DATE", "time", "VLCTL_ID"}, {"V_ID", "V_LICENSE_NO", "VLCTL_SMARTCARD_D_CODE", "D_NAME", "VLCTL_GPS_DATE", "time", "VLCTL_ID"})
in
    #"Expanded Count"

[Anonymous]

@Ahmedx ,

The result is correct. Thank you very much. but I dot know How to apply with the actual file. due to it work with advance editor and i dont have exp. on this. Would you mind checking  another(actual) file and how to solve the same problem. Thank you in advance. 

Sample 2 flie.

Sample2 

Best Regards,
JJ

[Ahmedx · Accepted solution]

see my video

How to fill missing.mp4

[Anonymous]

Update I followed your step and it work.

some rows still blank  which mean there is no D_Name Data for the whole day so it shown as blank is that correct?

[Ahmedx]

I think so, or show me where to get the data for these lines. if according to ID and date it is the first day.

[Anonymous]

OK. I think this is fine, Thank you very much

[Anonymous]

Thank you I will try follow your step with the actual data. I will update later .

[Ahmedx]

look at the screenshot and tell me what should be in the D_NAME column?

[Anonymous]

@Ahmedx 

Yes sir,   The answer should be MR TONY SIN

the logic is lookup from latest row that has D_Name based on same V_ID (License NO) and Same GPS_Date 

[Ahmedx]

will that be right?

[Ahmedx]

post this file too

C:\Users\008098\Desktop\SAMPLE.xlsx

[Anonymous]

@Ahmedx 

Hello,

here is the link to excel file
Excel File