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.

 

[GraH]

You can keep the previous result if you tweak the code a bit.

Reload the current result in a new query. Copy the source step code. Go to the original query, open the advanced editor and add a step at the beginning by pasting the copied copy. Rename the step to "previous" or alike. Wrap this code in Table.Buffer. this way PQ loads the full table in memory.
then at the end of the query, you add a step and do a merge with the new query you made earlier. Go again into the advanced editor and change the name of the referenced query by "previous".
in the UI extend the merges table and extract the date. Add a conditional column to take the original date when not null and use the current date where the original date is null. Erase the date columns you no longer need and rename the finale date to the samen name the original date has.
finally delete the second query you made as it is no longer needed.

 

[bear1]

This is genius, thank you so much. I guess the concept is you're using the copy as the source table and adding new rows from the source.

Do i keep Source as 2nd Step?, so i have Previous followed by Source

Previous = Table.Buffer(Excel.CurrentWorkbook(){[Name="NEW_LR_REPORT_IMPORT"]}[Content]),
Source = Excel.Workbook(Web.Contents("https://...............................................

At the end, i can't get Merge to work. The OK button is greyed out. In fact i can't merge to work at all.
I have tried Join Kind is Left Outer (all from first, matching from second) but there is very little data in the first column...is that the issue? And I've tried all the drop down options
Fuzzy match unchecked.
Also I can't get Append to work either.

To me this see the really interesting bit....how do i do this please? "Add a conditional column to take the original date when not null and use the current date where the original date is null."

 

[GraH]

Hi, sorry to reply do late. I was busy at work.
Maybe you can follow this tutorial where I picked up the trick.

 

[bear1]

Hi, sorry to reply do late. I was busy at work.
Maybe you can follow this tutorial where I picked up the trick.

Thank you. This is amazing. I think i'm so close.

I have merged the tables and edited the referenced query as follows:

= Table.NestedJoin(Previous, {"Index"}, COPY, {"Index"}, "COPY", JoinKind.LeftOuter)

I'm struggling with the next bit...."in the UI extend the merges table and extract the date".....what's not clear is in my first query should i have my Add Column "DateTime.FixedLocalNow()" or should i have a date column in my COPY query? Or no date column in either and just create one in the merged table. and if that last option is the case where do you extract the date from?

I assume i have the DateTime.FixedLocalNow() column in my first query. This is copied to the COPY query and then the merged query extracts the date from here if not null and if null insert Now()??????

Thank you for all the help...this is amazing.

rgds
bear1

 

[GraH]

In your excel table, the previous result, you would have a column with the load date.
So after the merge step you expand only this date column.
Now for the new rows, you don't have this date available. Here you use your current formula. Finally you add a conditional column: if the previous date is null then your formula else use the previous date.
In order to keep the query going next time, delete all the temporary date columns: the previous one and your current datetime fx one. Rename the final date column so it matches the columnname you used before.

 

[bear1]

In your excel table, the previous result, you would have a column with the load date.
So after the merge step you expand only this date column.
Now for the new rows, you don't have this date available. Here you use your current formula. Finally you add a conditional column: if the previous date is null then your formula else use the previous date.
In order to keep the query going next time, delete all the temporary date columns: the previous one and your current datetime fx one. Rename the final date column so it matches the columnname you used before.

I'm sorry im just not getting it. I have it as follows:

Query 1 (New LR Report Import) is an import from an external web source. No date column. And i don't add one.
Query 2 is my Copy of Query 1. Do i add a Load Date column here to the result table? and then the Query will re-load to Query 1 with a Load Date Column?

Then i merge Query 1 and Query 2.
Then i do this = Table.NestedJoin(Previous, {"Index"}, COPY, {"Index"}, "COPY", JoinKind.LeftOuter)
Then i Expand Load Date Column Only

This gives me a COPY.Load Date

I then add a custom column with DateTime.FixedLocalNow(), to give blank rows a DateTime.

I then use a conditional column to use the Copy.Load Date unless it's blank(null) in which case use the Date.FixedLocalNow() column

Then remove all the unnecessary columns....

 

[GraH]

Indeed you are close as I read this.
Hoping to clarity here. You must only have 1 query in the end. And in this table, loaded to Excel, you will have your imported date column. Otherwise I'm Hettinga lost.
So first do your standard query, without the magic mojo and load it excel.
Then revisit the query and do the mojo. Where in essence you merge the query with it's previous result (which you buffered).
You do not work on a copy. You only load the excel table in PQ to have the correct code generated for you.

 

[bear1]

Hiya,

Thank you so much for all the help so far but i just can't get this to work.

Again, i get the concept of taking the previous result and merging it with the new result and then new rows appear with a null date which the formula then fills.

Can you walk me through this step by step please...

1. Run a query to extract data from the web. Call this ORIGINAL DATA. It has no date. So i manually add a date column (ORIGINAL DATE).

2. Then load this New Table with Date to a Query from Table. Call it ORIGINAL DATA QUERY WITH DATE.

3. Run a 2nd query. Call it NEW DATA QUERY.

4. Copy the source of ORIGINAL DATA QUERY WITH DATE and past it in the advance editor of NEW DATA QUERY. Paste it above Source and name it Previous =.... and put Table.Buffer before it with a comma at the end. Have I got the order right? Previous then Source?

5. I can then delete ORIGINAL DATA.

6. Then merge NEW DATA QUERY with ORIGINAL DATA QUERY WITH DATE. Then i can delete ORIGINAL DATA QUERY WITH DATE.

7. I think i know what to do after this.

kind regards
bear 1

 

[GraH]

Hi again,
Sorry, I'm a bit too busy at the moment.
Could you provide a sample set, or the link to the web table.
Then I can share the code that hopefully sheds some light.