Power Query! Marge date fields to create datetime field

[RedllowFenix]

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!

 

[sandy666]

Re: Help Power Query! Marge date fields to create datetime field

maybe

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Year[/td][td=bgcolor:#5B9BD5]Month[/td][td=bgcolor:#5B9BD5]Day[/td][td][/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

2018​

[/td][td=bgcolor:#DDEBF7]08[/td][td=bgcolor:#DDEBF7]

25​

[/td][td][/td][td=bgcolor:#E2EFDA]

25/08/2018​

[/td][/tr]
[/table]

 

[RedllowFenix]

Re: Help Power Query! Marge date fields to create datetime field

maybe

[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]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Year[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Month[/COLOR][/TD]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Day[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Date[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]

2018​

[/TD]
[TD="bgcolor: #DDEBF7"]08[/TD]
[TD="bgcolor: #DDEBF7"]

25​

[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]

25/08/2018​

[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for the quick response. Could you tell me where I have to put the code? or how to?

 

[sandy666]

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

Source = Excel.CurrentWorkbook(){[Name="[B][COLOR="#FF0000"]Table24[/COLOR][/B]"]}[Content],

change red to your table name

for future:

PowerQuery aka Get&Transform

 

[RedllowFenix]

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?

 

[sandy666]

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

 

[RedllowFenix]

Re: Help Power Query! Marge date fields to create datetime field

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

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"

 

[sandy666]

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:

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

edit:

[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

 

[sandy666]

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

 

[RedllowFenix]

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!!