Forum Discussion
Convert YYMM into proper date
Hi, Anonymous ;
You colud use 3 methods to solve it , As Community Champion said, first in power query , another is create a column ,last create a measure. and the error you mentioned is because the measure you created is less than max or min. here are three ways：
Method1: add a custom column in power query.
#date( Number.FromText("20" & Text.Start([YYMM], 2)), Number.FromText(Text.End([YYMM], 2)), 01)Method2:Create a column in powerbi
date = DATE("20"&LEFT([YYMM],2),RIGHT([YYMM],2),1)
Method3:Create a Measure in powerbi.Measure = DATE("20"&LEFT(MAX([YYMM]),2),RIGHT(MAX([YYMM]),2),1)Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
selimovdthat looks exactly like what i need, unfortunately being so new to powerquery, im not sure of the correct syntax for using it. It reports an error in the power query editor.
Anonymous You're in Power Query, that's a totally different language...
There you can add a new column with the following code:
#date( Number.FromText("20" & Text.Start([YYMM], 2)), Number.FromText(Text.End([YYMM], 2)), 01)
Check my full example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI0MFOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YYMM = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each #date(Number.FromText("20" & Text.Start([YYMM], 2)), Number.FromText(Text.End([YYMM], 2)), 01)),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
#"Changed Type"