Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Power Query! Marge date fields to create datetime field

  1. #1
    New Member
    Join Date
    Oct 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query! Marge date fields to create datetime field

    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!

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

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

    maybe

    Code:
    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"
    Year Month Day Date
    2018
    08
    25
    25/08/2018
    Last edited by sandy666; Feb 27th, 2019 at 09:40 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Oct 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by sandy666 View Post
    maybe

    Code:
    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"
    [COLOR=#FFFFFF ]Year[/COLOR] [COLOR=#FFFFFF ]Month[/COLOR] [COLOR=#FFFFFF ]Day[/COLOR] [COLOR=#FFFFFF ]Date[/COLOR]
    2018
    08
    25
    25/08/2018
    Thank you for the quick response. Could you tell me where I have to put the code? or how to?

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Cool 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="Table24"]}[Content],
    change red to your table name

    for future:

    PowerQuery aka Get&Transform
    Last edited by sandy666; Feb 27th, 2019 at 10:08 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    New Member
    Join Date
    Oct 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Cool 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 by sandy666; Feb 27th, 2019 at 11:24 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  7. #7
    New Member
    Join Date
    Oct 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by RedllowFenix; Feb 27th, 2019 at 11:24 AM.

  8. #8
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Default 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:
    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"
    you need test it because I am using local date: dd/mm/yyyy like most countries
    Last edited by sandy666; Feb 27th, 2019 at 11:36 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,793
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    1 Thread(s)

    Default 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 by sandy666; Feb 27th, 2019 at 12:20 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    New Member
    Join Date
    Oct 2017
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •