Create date field from 2 columns in Power Query

Question

Hi...

I need to concatenate 2 columns to create a date column in format MMMYYYY.

I tried to convert the "Año" column to date but obtain a wrong data or create a new column to transform "Año" column to Year, not worked.

 I need a table field like this: 09/2019, like a field date.

Anybody can help me?

Thank you!!!

Answer 1

You should not be using my code - you should be performing those steps as you create the query. Excel will handle the correct syntax and use the correct names of the data fields.

Alternatively, you could create the formatted date string using a variation of Ashish's suggestion: in the custom column dialog:

= Date.ToText(#date([Ano],[Mes],1),"MMMyyyy")

which will add code like this:

= Table.AddColumn(#"Changed Type", "Custom", each Date.ToText(#date([Ano],[Mes],1),"MMMyyyy"))

Note that I don't know how to add the n with a tilde that you have Ano - so you need to modify my example or build the function in the dialog using the Insert button after selecting the field.

Answer 2

Hi,

Use the #date function in the Query Editor - https://docs.microsoft.com/en-us/powerquery-m/sharpdate

Hope this helps.

Answer 3

The easiest thing to do is to add a "Column From Examples"   In the first cell of your example column, use a date value like 9/1/2019  and if you have another month other than 9 then use that month as well.  When the dates are correct for all rows, do ANOTHER column from examples, but this time type out your dates in the preferred format Sep2019, etc., giving examples until they are all correct.  Then you can delete the first example column or even your year and month columns as well:

Here is the query that I recorded .... I'm in the US so that is my default date format.

    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([Month], "en-US"), "/1/", Text.From([Year], "en-US")}), type text),

    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged", type date}}),

    #"Added Custom Column" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine({Date.ToText([Merged], "MMM"), Text.From([Year], "en-US")}), type text),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"Merged"})

Answer 4

Thank you Bernie.

I don´t know if I´m doing wrong, I have an error...

Sorry but I´m not an expert in Power Query.

Answer 5

Thank you!!!

Finally works!!!