Excel Forum

Date & Time Transformation

Alistair Walker

Member

Members

Dashboards

Power Query

Power Pivot

Power BI

Forum Posts: 9

Member Since:
February 24, 2021

Offline

January 7, 2023 - 7:19 pm

I've imported from the web data that displays date as 1 to 31 and time in the format 12.01 (the separator being '.' I want to transform the date to format 01/01/2023 to 31/12/2023 and time to format 12:01 This only seems to work if each cell is changed manually. Any solutions? Thanks

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Trusted Members

Moderators

Power BI

Forum Posts: 486

Member Since:
January 31, 2022

Offline

January 7, 2023 - 8:07 pm

Could you please upload a file that contains the raw data? That makes it easier to come up with a solution. Thank you!

Alistair Walker

Member

Members

Dashboards

Power Query

Power Pivot

Power BI

Forum Posts: 9

Member Since:
February 24, 2021

Offline

January 7, 2023 - 9:05 pm

Hello Riny,

Attached is the raw data file. Alistair

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Trusted Members

Moderators

Power BI

Forum Posts: 486

Member Since:
January 31, 2022

Offline

January 7, 2023 - 9:17 pm

Thanks for the file. Just wondering how you would know which month we are dealing with. Obviously, your example relates to January as that is what is written in the Sheet name. Will you always receive csv files with the name of the month in it?

And just to be sure, a time reference like 8.3 will be thirty minutes past 8, whereas 10.05 will be 5 minutes past 10. Correct?

Alistair Walker

Member

Members

Dashboards

Power Query

Power Pivot

Power BI

Forum Posts: 9

Member Since:
February 24, 2021

Offline

January 7, 2023 - 9:33 pm

Hi Riny,

https://www.trggpattaya.com/schedule/

This is the link to the webpage. The new month is added to the website each month and identified only by the title January 2023, February 2023 etc

Time references are displayed on website as 08.30 10.05 etc. Importing them truncates zeros so 08:30 becomes 8.3

The file Raw Data I loaded it without transforming anything.

Thanks
Alistair

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Trusted Members

Moderators

Power BI

Forum Posts: 486

Member Since:
January 31, 2022

Offline

January 7, 2023 - 10:13 pm

Hi Alistair,

Did a quick-and-dirty query that does what you need, I believe. I made no attempts to make it look nice, but the steps aren't difficult to follow. It should also work when February is added to the web-site.

See if it works for you.

Riny

Alistair Walker

Member

Members

Dashboards

Power Query

Power Pivot

Power BI

Forum Posts: 9

Member Since:
February 24, 2021

Offline

January 9, 2023 - 4:27 pm

Hi Riny,

Thanks for your work on this much appreciated. I've downloaded and will analyse the steps to understand how you transformed it.

Kind regards