Power Query! Marge date fields to create datetime field

RedllowFenix

New Member
Joined
Oct 5, 2017
Messages
18
Hi there,

I have three columns in a Power Query Table in Excel with Year, Month, Day (they're numbers, for instance: 2018,08,25) and I would like to merge them in a date column (DD/MM/YYYY). I searched in Google for a solution and tried everything but nothing works, it just give me an error when the columns are merged.

I'm newbie on this (power query) so I don't know how the M language works.


I would appreciate your help.

Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Re: Help Power Query! Marge date fields to create datetime field

maybe

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Month", type text}, {"Day", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Day", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
    #"Changed Type1"[/SIZE]

YearMonthDayDate
2018​
08
25​
25/08/2018​
 
Last edited:
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

maybe

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Month", type text}, {"Day", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Day", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
    #"Changed Type1"[/SIZE]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Year[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Month[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Day[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR]
2018​
08
25​
25/08/2018​

<tbody>
</tbody>

Thank you for the quick response. Could you tell me where I have to put the code? or how to? :rolleyes:
 
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

Don't quote whole post! this is not necessary.

create table as my blue one
Data - From Table
Advanced Editor - replace code there with code from the post
in this line
Code:
Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#FF0000"]Table24[/COLOR][/B]"]}[Content],
change red to your table name

for future:

PowerQuery aka Get&Transform
 
Last edited:
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

Thanks.

I followed the steps, changed the table name but it results in an ERROR.

What am I doing wrong?
 
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

1. what is your table name?
2. post your code here (use tags)

or check this link: to date dd/mm/yyyy
 
Last edited:
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

1. Table1. It has the same header names and same data

Code:
let    
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Month", type text}, {"Day", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Day", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}})
in
 #"Changed Type1"
 
Last edited:
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

probably because you have US style date (system date) mm/dd/yyyy

use change type with Locale and set English - Country that has date type dd/mm/yyyy

something like:
Code:
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Date", type date}}, "en-US")

edit:

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}, {"Month", type text}, {"Day", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Month", "Day", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Merged Columns", {{"Merged", type date}}, "en-US")
in
    #"Changed Type with Locale"[/SIZE]

you need test it because I am using local date: dd/mm/yyyy like most countries
 
Last edited:
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

also you can try

PQ editor
File - Options and settings - Query Options - Regional Settings (English (United Kingdom)
then set whole M-code again

or

stay with date as text

or

change regional system date
 
Last edited:
Upvote 0
Re: Help Power Query! Marge date fields to create datetime field

It works! Indeed, I have my computer on English (although I'm in Colombia but I like to have the computer system on English for learning purposes) so my system date is mm/dd/yyy.

Thank you so much!!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top