DateTime Stamp is one or more columns change in a record

I want to add a DateTime stamp to log that a record has changed. I have created a DateTime stamp using Table.ReplaceValue but it is updating the [Edit Date] field for all records even those where the selected field hasn’t changed in other records.

This is what I have done.

*DateStampDOB = Table.ReplaceValue(#"Expanded kidsinschools",each [Edit Date], each if [Edit Date] =[kidsinschools.Edit Date] then null else DateTime.FixedLocalNow() ,Replacer.ReplaceValue,{"Edit Date"}),*

Any ideas how I can do this? Any help would be greatly appreciated! Thanks Erica

Hi @Ericadyson,

So I formatted your code so we can go over what is happening in smaller steps…

  1. Your Step name
  2. M function that is called and your previous step as table to transform
  3. for each old value
  4. replace with each new value
  5. replacer as function
  6. column(s) to search

Please know that “the DateTime stamp to log that a record has changed” will update every time the query is refreshed… If you want to conditionally update a value, instead of Transforming a column, go for adding a Custom- or Conditional Column instead.

I hope this is helpful.

Thanks Melissa. I think I understood what the code does. Of course, silly me, the DateTime stamp will update on every refresh. That’s useless for this purpose - the opposite of what I need!

In the end, I have done a bit of convoluted step by step query:

  1. I create a new column in my Merge if certain data columns change from the current data. I have to do this 3 times ie for each field that might be updated.

  2. Then I merge those columns to create one Changed column,

  3. Then I append those records to the master table. Then GroupBy and create another helper column that marks records for deletion where GroupBy =2 and the Changed Column = null.

  4. Then I filter out those records marked for deletion.

This gives me the correct answer and I only need to refresh and all updates the next time I get addtional records or changed data of existing records.

BUT, I am sure there is a much slicker way to do this.

Many thanks for the tip about DateTime.FixedLocalNow. I should have realized this! But I was trying to get an automatic date entry when a record changes. Didn’t succeed in that, but somehow, I’ve got the answer I need!

Hi @Ericadyson,

I’d be happy to explore alternatives with you, but then you’ll have to supply some sample data with the expected end result, else please mark the thread as solved.

Thank you.

1 Like

That’s great Melissa… That’s extremely kind of you.

As you know, most of my stuff is in hieroglyphics (ie Hebrew), so I’ll prepare a file with English field names etc so you can see what I’m up to! So thanks a lot and ‘see you soon’!

1 Like

Hi Melissa

I’m attaching 2 files. Here’s the deal.

I got a master file of around 2000 records. I had to correct a whole series of telephone numbers (about 8 columns I think). That generated a file called CombinedTelNumbers. I use that as the ‘master’.

Separately, I got a list of kids who should be on the master list (but I discovered that some weren’t), 4 things to do here - 1) Add missing records 2) Update the ‘master file’ if the Tel 1, DOB or Street has changed. 3) Make sure that I can identify the edit date for additions and record changes so users can filter on the edit date. 4) Make sure the age is dynamic for all records.

I’ve done all this in PQ and then I would load both the Schools file and the Updated file to BI. The 2 will be linked via ID. The schools table has more details about the school.

The users aren’t experienced data people in any way shape or form. They will get a table that they can filter any which way, so they will get what seems to them like 1 table with a number of fields and then a whole series of filters.

Any suggestions on how to make my convoluted approach slicker would be greatly appreciated. I’ve put key field names in English and you can of course add/remove dummy data to the Schools file. You can see that I’ve done that already.

Attached are the 2 files … source כספת נצורה מעובד כולם 8.20 telephone numbers v3 for Melissa.xlsx (1.3 MB) בית הספר גולני schools 077 for melissa.xlsx (25.1 KB)

Hi @Ericadyson,

I have no objections to the step by step, overall process you’ve worked through.
However I did manage to slim a couple of your queries down a bit, you’ll find those in the folder on top.

This is the only file I updated. source כספת נצורה מעובד כולם 8.20 telephone numbers v3 for Melissa.xlsx (2.8 MB)
Note that if you switch the SecondFileLocation parameter back, all queries will be restored.
I hope this is helpful.

1 Like

Wow Melissa. What a lot of work you’ve done… and how did you manage with the Hebrew? Fantastic. I have some learning to do regarding _ in PQ and the combining of steps that you’ve done. Some I got, but some were too difficult for me to grasp at a glance. You’ve given me some practical homework! Particularly useful will be the telephone conversion M code… which converted them all at once and of course, I will study (or probably more likely, copy) the one step fx for age calculations. So you’re helping me no end to ‘move up a notch’ with M code! So off I go to learn some more, step by step! Many many thanks. Erica

2 Likes

Glad I could help @Ericadyson

The Hebrew was a challenge indeed :wink:
Let me know if you have any questions - I’d be happy to provide additional context - where I can.

All the best.

1 Like

Thanks so much. There are a couple that I want to get my head around - the ‘merge multiple telephone numbers’ step/s and the code you did for creating one "changed’ column if any of a number of different fields change. Both of those code steps will be very very reusable. The telephone one was easier for me to understand. I’m going to have a go myself to see if I can repeat (with understanding) and hope it will be OK if I get back to you WHEN (not if) I get stuck! Mega thanks. I truly value and appreciate your help and patience.

Ah … one question. Is it the best approach to do all this in Excel PQ and then to upload the result into BI or to do all this in BI and upload the ‘raw data’?

Yup I cheated :wink:

For the telephone numbers you added a couple of steps, you combined all elements into a single list for each separate number, combined the list elements and later went on to rename and reorder them.


To achieve the same, I nested each List.Combine inside the Text.Combine function (that’s the extract part to retrieve all the elements from the list). Placed them in the desired order and gathered all results in a new list - using the list initializer { }
Could have also wraped the ExtractTel step around those list initializers but decided to show the intermediate list result - however should you want to do that - this is what that would look:

UpdateTel =  Table.AddColumn(#"Added zero column", "new Tel", each
    Text.Combine(List.Transform(
        {   Text.Combine(List.Transform(List.Combine({{[Zero]},{[קידומת טלפון 1]},{[טלפון 1]}}), Text.From)),               // "tel 1", "טל 1"
            Text.Combine(List.Transform(List.Combine({{[Zero]},{[קידומת טלפון 2]},{[טלפון 2]}}), Text.From)),               // "Tel 2", "טל 2" 
            Text.Combine(List.Transform(List.Combine({{[Zero]},{[קידומת טלפון 3]},{[טלפון 3]}}), Text.From)),               // "Tel 3", "טל 3"
            Text.Combine(List.Transform(List.Combine({{[קידומת טלפון 4]},{[טלפון 4]}}), Text.From)),                        // "tel 4", "טל 4"
            Text.Combine(List.Transform(List.Combine({{[Zero]},{[קידומת טלפון ממונה 1]},{[טלפון ממונה 1]}}), Text.From)),  // "tel memune 1", "טל ממונה 1"
            Text.Combine(List.Transform(List.Combine({{[Zero]},{[קידומת טלפון ממונה 2]},{[טלפון ממונה 2]}}), Text.From)),  // "tel memune 2", "טל ממונה 2"
            Text.Combine(List.Transform(List.Combine({{[Zero]},{[קידומת טלפון ממונה 3]},{[טלפון ממונה 3]}}), Text.From))   // "tel memune 3", "טל ממונה 3"
        }, Text.From), ";"), type text 
    ),

And finally in the SplitTel step you can actually name the columns that get created (in yellow).

.

You could say I did something similar for the AddCheck step


But instead of a list I created a record - using the record initializer [ ]
And this is what that record looks like, it has 3 fields, one for each check and returns a list generated by either the then or else clause of the if-statement for that field.

image

Now we are only interested in the lists of course so using Record.FieldValues I retain the lists and “lose” the record field names. Finally with List.Combine I turn these 3 list into one single list.

I hope this helps demystify these key steps for you - let me know if anything is unclear.

.

As for doing the workload in Excel vs Power BI - I’d always go for PBI:

  1. Power Query itself is more up to date in Power BI compared to Excel
  2. Performance is generally better in Power BI because it allows parallel loading which Excel does not
2 Likes

Once again, thanks a million. 1) I think I’m ‘getting it’ thanks to your explanation and I think (!) I’ve finally grasped what the _ (underscore) means inside a step - ie when to use it. I need to go through this and try it myself but I’ve already moved up a notch in how to combine steps. This is new for me. Need to find time to do this.

Re BI vs Excel to do the transformations. Oh… OK. I thought we had to do whatever transformation we could outside of BI. It makes it easier to maintain then everything is in one place… but that wasn’t so much my concern, more I wanted to be sure I was using best practice. Now I know. Can’t wait to find some time to practice combining steps!! I’m “hooked”!

In short that’s a nameless variable that can be used to access the current record (read: “row”).

Good luck on your “combining steps” efforts :+1:
All the best!