Forum Discussion

matty_pants's avatar
matty_pants
Frequent Visitor
7 years ago
Solved

Modify timestamp across many (20) tables by batch process

I have two dozen tables that I need to merge into one table and the common row value, the Timestamp, has a slight variation in the seconds from table to table. Is there a way to batch change the time...
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    Hi matty_pants,

    unfortunately, you have to add a step to every table.
    Unless you combine all tables into one table at first and then transform the column Timestamp at once. It depends on what your next step is going to be.

     

    An example how to use the conversion:

    let
    Source = Sql.Databases("10.7.10.186\RCARCHIVE"),
    #"ROY BICKELL SCHOOL" = Source{[Name="ROY BICKELL SCHOOL"]}[Data],
    dbo_1000MTL24 = #"ROY BICKELL SCHOOL"{[Schema="dbo",Item="1000MTL24"]}[Data],
    TimestampWithoutSeconds = Table.TransformColumns(dbo_1000MTL24, {{"Timestamp", each #time(Time.Hour(_), Time.Minute(_), 0)}})in
    TimestampWithoutSeconds
matty_pants's avatar
matty_pants
Frequent Visitor
7 years ago

Thanks Nolock. How does I implement this? And will it apply to timestamps across many tables at once? (I've updated the link to something outside DB).

Nolock's avatar
Nolock
Resident Rockstar
7 years ago

Hi matty_pants,

unfortunately, you have to add a step to every table.
Unless you combine all tables into one table at first and then transform the column Timestamp at once. It depends on what your next step is going to be.

 

An example how to use the conversion:

let
    Source = Sql.Databases("10.7.10.186\RCARCHIVE"),
    #"ROY BICKELL SCHOOL" = Source{[Name="ROY BICKELL SCHOOL"]}[Data],
    dbo_1000MTL24 = #"ROY BICKELL SCHOOL"{[Schema="dbo",Item="1000MTL24"]}[Data],
    TimestampWithoutSeconds = Table.TransformColumns(dbo_1000MTL24, {{"Timestamp", each #time(Time.Hour(_), Time.Minute(_), 0)}})in
    TimestampWithoutSeconds