Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Any help on how to transform a date column supplied as YYMM (2106 for June 2021) into a full date? any day can be used, 1st of the month for arguments sake.
I tried splitting column, and replacing values but not joy. Transforming into date assumes the integer is the day so 02 becomes 02/01/1970
Solved! Go to Solution.
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"
Hey @Anonymous ,
you can separate the parts and put them together again:
myDate =
VAR vYear = LEFT(myTable[YYMM], 2)
VAR vMonth = RIGHT(myTable[YYMM], 2)
RETURN
DATE( 2000 + vYear, vMonth, 1 )
Ok, so i found 'new measure' which allows me to add a Dax expression which seems to match the syntax of this reply. (couldnt get the power query answer working)
I receive the following error though:
"A single value for column 'ssYYMM' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
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.
that doesnt change anythig @selimovd , it still detects an error in the same place.
moving the line to in from let and i get a token eof expected?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
142 | |
133 | |
110 | |
68 | |
55 |