Convert YYMM into proper date

[PhilN]

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

[PhilN]

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

[selimovd]

@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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[PhilN]

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

[v-yalanwu-msft · 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)

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.

[PhilN]

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?