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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.