Log In
Register
Home
May 13, 2021
Offline
Hi-
my month column has the name of the month ie January/February listed. Is there a way for Query to recognize it as a Date/Month vs text?
See Answer
VIP
Trusted Members
December 7, 2016
Offline
May 13, 2021
Offline
Thank you- so if I don't have a year I'm stuck?
July 16, 2010
Offline
Hi Moriel,
Welcome to our forum!
Like Anders suggested, you can use the Date.FromText function. If you don't have a year column in your data you can add it by entering it in the formula like so:
= Date.FromText("2021-"&[Month]&"-1")
Where [Month] is the name of the column containing your month names.
This will return a list of dates as at the first of each month name for 2021.
Mynda
Answers Post
May 13, 2021
Offline
Firstly- Thank you very much for your help.
Second, I tried what you did- see below and I get the following error: Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
My Column's name is Month and the source data isn't consistent with the format so first I made it all lower and then wanted to add the date.
#"Lowercased Text" = Table.TransformColumns(#"Renamed Columns3",{{"Month", Text.Lower, type text}}),
#"Added Conditional Column" = Table.AddColumn("Lowercased Text", Date.FromText("2021-"&[Month]&"-1"))
July 16, 2010
Offline
Hi Moriel,
Please share an extract of your Excel file that illustrates the error.
Mynda
May 13, 2021
Offline
Here is my file-
Thank you,
July 16, 2010
Offline
Hi Moriel,
Your file didn't get uploaded. After selecting the file you need to click the 'start upload' button and wait for the green text notification that appears briefly below the attachment window to tell you the file has finished uploading.
Mynda
May 13, 2021
Offline
Mynda-
Thank you for your patience. Here it is.
July 16, 2010
Offline
Hi Moriel,
Thanks for sharing your file. Unfortunately, the source data is somewhere else, so when I open the query I get an error. However, I used the data on the sheet called 'table' and was able to add the calculated column. My step looks like this:
= Table.AddColumn(#"Lowercased Text", "Month Number", each Date.FromText("2021-"&[Month]&"-1"))
Whereas yours looks like this, with no reference to the previous step or maybe it's the column name missing, either way, there's an argument missing from the formula:
= Table.AddColumn("Lowercased Text", Date.FromText("2021-"&[Month]&"-1"))
Hope that helps.
Mynda
1 Guest(s)
