Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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



Need Help

12 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    post this file too

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

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
    Ahmedx
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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