cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matty_pants
Frequent Visitor

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 timestamp to make the seconds null across the tables? The timestamps increase by 30 minutes across all tables otherwise. 

 

The pbix file is found here:  https://filebin.net/4kxvw3rpw0efyd4g/2019-07-10_Roy_Bickell_RCA_Trends.pbix?t=6v8cdunf  (New location that doesn't require Dropbox account).

 

Thanks!

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
Nolock
Resident Rockstar
Resident Rockstar

Hi @matty_pants,

unfortunately, I can't open your sample because only registered users of Dropbox can access the file.

But if I understand you right, you want to remove seconds from a timestamp.

I'd try to create a new timestamp from hours and minutes.

each #time(Time.Hour([Timestamp]), Time.Minute([Timestamp]), 0)

You could also just substract seconds but I don't recommend that because there can also be a subsecond part like 10:20:30,123 which has 123 milliseconds.

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).

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Kudoed Authors