Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would find the syntax to convert an existing Date column into text, in the "dd/MM/yyyy" format, but do within the column, not by adding a new column. Using Date.ToText([Start Date], "dd/MM/yyyy") as the conversion code.
Tried using Transform and Change Type on the column and edit the resulting code, but keep getting an error, so must not have the syntax correct.
Cheers
Solved! Go to Solution.
PQ is very sensitive to Date versus DateTime. Sometimes i have to convert to a DateTime. Below is some sample code.
Regards,
Mike
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Extracted Date" = Table.TransformColumns(Source,{{"myDate", each Date.ToText(Date.From(_),"dd/MM/yyyy") , type text}}) in #"Extracted Date"
My goal is to get the data for today (so whenever we update the datasource. As the combination of date & text in the web.Contens() is not possible, we had to convert to it to text in an "yyyy-MM-dd".
Solved it like that.
= Json.Document(Web.Contents("https://api.xxxx/client?json=1&from=" & Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd") & "&to=" & Date.ToText(Date.From(DateTime.LocalNow()),"yyyy-MM-dd") &"T23:59:59Z"))
let
Source = let StandardDate = (dateColumn as text,DateSchema as text) =>
let
MyDelimiter = Text.Range(DateSchema,Text.PositionOfAny(DateSchema,{"/","-",".","\","_"}),1),
DateList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(dateColumn),
SchemaList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(DateSchema),
TableDate = Table.FromColumns({DateList,SchemaList}),
myDay = "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "D" )[Column1]{0}),
myDD = Text.Range(myDay,Text.Length(myDay)-2,2),
MyMonth= "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "M" )[Column1]{0}),
myMM = Text.Range(MyMonth,Text.Length(MyMonth)-2,2),
myYear= "20" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "Y" )[Column1]{0}),
myYYYY = Text.Range(myYear,Text.Length(myYear)-4,4),
DateByParts = myYYYY & "-" & myMM & "-" & myDD,
DateCleaned = Date.FromText(DateByParts)
in DateCleaned
in StandardDate
in
Source
Hi @PhilC ,
My standard date is mm/dd/yyyy. So: the first pic is date, second pic is text. Included is the code, but what I did was go to Date Type, and change it to text.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi Nathaniel,
My dates are not currently formatted, so I have M and MM dates. I need to have dd/MM/yyyy as I am going to compare to another dateset on a text basis, so am looking to have a consistent text format.
I have tried using Change Type on the column but it does not alter for format to expand M to MM with a preceeding 0, which is what I need.
PQ is very sensitive to Date versus DateTime. Sometimes i have to convert to a DateTime. Below is some sample code.
Regards,
Mike
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Extracted Date" = Table.TransformColumns(Source,{{"myDate", each Date.ToText(Date.From(_),"dd/MM/yyyy") , type text}}) in #"Extracted Date"
DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
23/09/2022
Hi Phil,
I actually could not tell if your original data the any type, a text or a datetime. Date.From usually does not care, so I suggested using it.
Hope this explanation helps,
Mike
Hi Mike, @Anonymous
That solved it for me. The Date.From(_) bit was the key I think. Can you explain that?
Cheers
@PhilC ,
So you need to get the date format changed first?
Nathaniel
Proud to be a Super User!
Table.AddColumn(#"Changed Type1", "Custom", each Date.ToText([Column1], "dd/MM/yyyy"))
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN7LUNzIwtFSK1QFzDaG8WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.ToText([Column1], "dd/MM/yyyy")), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}, {"Column1", type text}}), #"Custom1" = Table.TransformColumnTypes(#"Changed Type1",{{"Column1",each Date.FromText([Column1],"dd/MM/yyyy",}}) in Custom1
@PhilC , no success so far trying to emulate adding a custom column.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |