Fixed Date Time

[bear1]

My source data has no date. I use Power Query to pull in the data and add column “Imported date”, using DateTime.FixedLocalNow(). Which works perfectly. However when I refresh the query it overwrites all the previous dates with the LocalNow() time. I want just the new data coming in to have the LocalNow() time and for the previously imported records to keep the initial datetime, I.e don’t overwrite the initial datetime.

All I want is to import new data only with current date time and not the older records from the source data.

Any help would be greatly appreciated I’m going a little crazy.

 

[bear1]

https://howerobinson-my.sharepoint....JDoCHGxZhaqLwBlICy3iFtRGZ0UuVE_UB3kQ?e=PcVD2J

 

[GraH]

let
    Previous = Table.Buffer(Excel.CurrentWorkbook(){[Name="Source_2"]}[Content]),  // change name to the table name of your query (so the result it generates)
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content], // this needs to be the link to the web, which I can't access
    MergePrevious_JT_LA = Table.NestedJoin(Source, {"Some data"}, Previous, {"Some data"}, "Previous", JoinKind.LeftAnti), // here you merge with Left Anti join, to only keep the new rows
    RemoveJoinCol = Table.RemoveColumns(MergePrevious_JT_LA,{"Previous"}),// remove the added column to make the join
    SetTypes = Table.TransformColumnTypes(RemoveJoinCol,{{"Some data", type text}}), // here you start your transformation steps
    AddDate = Table.AddColumn(SetTypes, "Load Date", each DateTime.LocalNow(), type datetime), //here you add your load date, only for the new data
    AppendPrevious = Table.Combine({AddDate,Previous}) // Appending the previous data back in the result
in
    AppendPrevious

 

[bear1]

This is amazing, thank you so much.

And i feel bad asking again but I still have an issue. I CAN get this to work but the result keeps adding rows each time i run it and add a date in Column1 as well as LOAD DATE. I think i need an Index column to make it work...when i do insert an Index column? Do i need an index column many of the first few rows in column1 are blank.

This is the code i have....this works but gives below result.

Previous = Table.Buffer(Excel.CurrentWorkbook(){[Name="NEW_LR_REPORT"]}[Content]),
Source = Excel.Workbook(Web.Contents("https://XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX).xlsx"), null, true),
#"NEW LR REPORT_Sheet" = Source{[Item="NEW LR REPORT",Kind="Sheet"]}[Data],
MergePrevious_JT_LA = Table.NestedJoin(Source{[Item="NEW LR REPORT",Kind="Sheet"]}[Data], {"Column1"}, Previous, {"Column1"}, "Previous", JoinKind.LeftAnti),
RemoveJoinCol = Table.RemoveColumns(MergePrevious_JT_LA,{"Previous"}),
SetTypes = Table.TransformColumnTypes(RemoveJoinCol,{{"Column1", type text}}),
AddDate = Table.AddColumn(SetTypes, "Load Date", each DateTime.LocalNow(), type datetime),
AppendPrevious = Table.Combine({AddDate,Previous})
in
AppendPrevious

 

[bear1]

let
    Previous = Table.Buffer(Excel.CurrentWorkbook(){[Name="Source_2"]}[Content]),  // change name to the table name of your query (so the result it generates)
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content], // this needs to be the link to the web, which I can't access
    MergePrevious_JT_LA = Table.NestedJoin(Source, {"Some data"}, Previous, {"Some data"}, "Previous", JoinKind.LeftAnti), // here you merge with Left Anti join, to only keep the new rows
    RemoveJoinCol = Table.RemoveColumns(MergePrevious_JT_LA,{"Previous"}),// remove the added column to make the join
    SetTypes = Table.TransformColumnTypes(RemoveJoinCol,{{"Some data", type text}}), // here you start your transformation steps
    AddDate = Table.AddColumn(SetTypes, "Load Date", each DateTime.LocalNow(), type datetime), //here you add your load date, only for the new data
    AppendPrevious = Table.Combine({AddDate,Previous}) // Appending the previous data back in the result
in
    AppendPrevious

View attachment 61929

Hi GraH,

I'm very pleased to say this is working well. Thank you so much for all your help.

I do need one small adjustment......using the above example...In the 'Some data' column i sometimes get the same name coming up. For example 'Bill J' might appear twice although it will have different criteria in other columns but with the current solution one row would be delete as it's assumed to be a duplicate. Somehow i need Power Query to check all columns (11) to see if they are indeed exact duplicates or just have the same name?

How could i amend it to do that and still keep the Load Date update with the new inputs.

Rgds
Bear1

 

[GraH]

Go to the join step and link all columns to each other that needs to checked. Press CTRL key and left mouse click in sequence.
Do remember to update the step again so it refers again to the table you have buffered.

 

[bear1]

Thank you, but i can't make it work....

I have all columns in the Join ( Left Anti) but lost after that. Ctrl + left mouse doesn't do anything.

let
Previous = Table.Buffer(Excel.CurrentWorkbook(){[Name="SOURCE_2"]}[Content]),
Source = Excel.CurrentWorkbook(){[Name="SOURCE"]}[Content],
MergePrevious_JT_LA = Table.NestedJoin(Source, {"Vessel", "Charterer", "Size", "Grade", "Load", "Disch.", "Date", "Unit", "Rate", "COMMENT", "Status"}, Source, {"Vessel", "Charterer", "Size", "Grade", "Load", "Disch.", "Date", "Unit", "Rate", "COMMENT", "Status"}, "Previous", JoinKind.LeftAnti),
RemoveJoinCol = Table.RemoveColumns(MergePrevious_JT_LA,{"Previous"}),
SetTypes = Table.TransformColumnTypes(RemoveJoinCol,{{"Vessel", type text}}),
#"Filtered Rows" = Table.SelectRows(SetTypes, each true),
AddDate = Table.AddColumn(#"Filtered Rows", "Load Date", each DateTime.LocalNow(), type datetime),
AppendPrevious = Table.Combine({AddDate,Previous}),
#"Reordered Columns" = Table.ReorderColumns(AppendPrevious,{"Load Date", "Vessel", "Charterer", "Size", "Grade", "Load", "Disch.", "Date", "Unit", "Rate", "COMMENT", "Status", "Index"}),
#"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each Text.Contains([Status], "FXD"))
in
#"Filtered Rows1"

 

[bear1]

Don't ignore my last. I think i've done it. Thanks. I did it in the editor rather than using the gear wheel.

 

[GraH]

have all columns in the Join ( Left Anti) but lost after that. Ctrl + left mouse doesn't do anything.

D'oh, I meant shift...