Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to 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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |