Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
PhilN
Frequent Visitor

Convert YYMM into proper date

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

1 ACCEPTED SOLUTION

Hi, @PhilN ;

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)

vyalanwumsft_0-1625471170868.png

Method2:Create a column in powerbi

date = DATE("20"&LEFT([YYMM],2),RIGHT([YYMM],2),1)

vyalanwumsft_1-1625471458386.png
Method3:Create a Measure in powerbi.

Measure = DATE("20"&LEFT(MAX([YYMM]),2),RIGHT(MAX([YYMM]),2),1)

vyalanwumsft_2-1625471622804.png

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.

View solution in original post

6 REPLIES 6
PhilN
Frequent Visitor

@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.

 

Screenshot 2021-07-01 161713.png

@PhilN 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"

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
selimovd
Super User
Super User

Hey @PhilN ,

 

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 )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

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, @PhilN ;

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)

vyalanwumsft_0-1625471170868.png

Method2:Create a column in powerbi

date = DATE("20"&LEFT([YYMM],2),RIGHT([YYMM],2),1)

vyalanwumsft_1-1625471458386.png
Method3:Create a Measure in powerbi.

Measure = DATE("20"&LEFT(MAX([YYMM]),2),RIGHT(MAX([YYMM]),2),1)

vyalanwumsft_2-1625471622804.png

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors