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!

 

[RedllowFenix]

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

Sandy, I also want to ask you, if you let me, a new request:

I have several excel files (monthly reports of my region's exports) that appends to another query forming a query consolidated. These monthly reports have the three columns which we discussed before (Year, Month, Day). What I want to do is create a new full date column dynamically, I mean that each time that I append a new monthly report to the query, Excel automatically creates the new date column.

¿Is that possible?

 

[sandy666]

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

try files from folder then append tables from these files create Date column
it should work for new files (tables) also

just add new file to the folder and refresh Query

 

[RedllowFenix]

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

Ok I get it. How the code looks like in this case? I need to preserve the three columns, it is just to add a new column.

 

[sandy666]

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

Code? Sure

[SIZE=1]let
    Source = Folder.Files("D:\fromfolder\for report"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from for report", each #"Transform File from for report"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Transform File from for report"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from for report", Table.ColumnNames(#"Transform File from for report"(#"Sample File"))),
//[COLOR="#FF0000"][B]here is the point where you can start do what you want.[/B][/COLOR] [B][COLOR="#0000FF"]All above is automated.[/COLOR][/B]
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Table Column1", "Conditions", each if [Year] = 0 then 0 else if [Year] = null then 0 else if [Month] = 0 then 0 else if [Month] = null then 0 else if [Day] = 0 then 0 else if [Day] = null then 0 else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Conditions] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Conditions"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Day", type text}, {"Month", type text}, {"Year", type text}}, "en-GB"),{"Day", "Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"[/SIZE]

 

[RedllowFenix]

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

Thanks sandy. It works through "Sorted Rows", where it gets an error.


I was wondering, why did you create a conditional column then it filter and remove. Is it not better just starts from "Merged columns"?

 

[sandy666]

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

this is an example of code wich works with specified conditions
you didn't show any your own example so treat code as theory only

 

[RedllowFenix]

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

Yo are right, excuse me!


Thanks again sandy, I just took the "Merged Columns" and it worked like a charm!

You are the boss!

 

[sandy666]

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

You are welcome

to say thanks and appreciate any of the post you can click Thanks/Like button at the left bottom corner in every post which helped you

have a nice day

 

[peter789]

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

Posted in error