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  a...
Need Help
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




    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

        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.