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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.