Convert YYYYMM to date

[jpt1228]

Hello, I have searched all over and I see many folks have the opposite issue.

I have a date column that is YYYYMM and I need to convert it into a date the date table can recognize. When I add a column in my date table with Format Date YYYYMM it won't let me due to duplicate YearMonth in the column. I want to convert 201709 to either the month/year (9/2017) or 9/1/2017)

[MarcelBeug]

Is your YYYYMM column: text or numeric?

Are you looking for a DAX or a Power Query (M) solution?

What is your code so far?

[Decal]

I would like to see how to do it in dax when coming from text 

[Ashish_Mathur]

Hi,

This calculated column formula will work

Date = 1*("1/"&right(Data[YYYYMM],2)&"/"&LEFT(Data[YYYYMM],4))

Hope this helps.

[Decal]

This converts is to a 5 digit number (e.g., 44197 for 2021-01).  I need a date.  Power BI won't let me format 44197 as a date

[Decal]

It worked to use
Date = 
DATE ( LEFT([YYYYMM field]),4),RIGHT([YYYYMM field],2),1)

Thank you all for the help

[jpt1228]

Hi @MarcelBeug I am using Power BI so I can use both I believe. I am more familiar with DAX. 

This is how the column was imported. I have tried converting the column to text and whole number but havent figured out the solution. It is a number column by default.

let
Source = Folder.Files("C:\Users\jthompson\Documents\Customer POS\C&S"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from C&S", each #"Transform File from C&S"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from C&S"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from C&S", Table.ColumnNames(#"Transform File from C&S"(#"Sample File"))),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Table Column1", each ([UPC] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Source.Name", type text}, {"Customer", type text}, {"Vendor_Name", type text}, {"UPC", type text}, {"Description", type text}, {"Pack", Int64.Type}, {"Size", type text}, {"Qty", Int64.Type}, {"Cost", type number}, {"Last_Yrs_Qty", Int64.Type}, {"Last_Yrs_Cost", type number}, {"MCase", Int64.Type}, {"YYYYMM", Int64.Type}, {"Grp_ID", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Customer] <> null)),
#"Extracted Text Range" = Table.TransformColumns(#"Filtered Rows", {{"UPC", each Text.Middle(_, 2, 11), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Text Range","-","",Replacer.ReplaceText,{"UPC"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Source.Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Source.Name.1", "Source.Name.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Source.Name.1", type text}, {"Source.Name.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Source.Name.2", "Vendor_Name", "Description", "Size", "Last_Yrs_Qty", "Last_Yrs_Cost", "MCase", "Grp_ID", "Pack"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Qty", "Case Sales"}, {"Cost", "Dollar Sales"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"YYYYMM", Int64.Type}})
in
#"Changed Type2"

[Decal]

I need to do this in Power BI, not Power Query

[MarcelBeug]

In the meantime I was preparing an answer: in Power Query you can add suffix "01"  and then change the column type to date.

let
    Source = #table(type table[YYYYMM = text],{{"201709"},{"201710"}}),
    #"Added Suffix" = Table.TransformColumns(Source, {{"YYYYMM", each _ & "01", type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Suffix",{{"YYYYMM", type date}})
in
    #"Changed Type"

[Decal]

I need to do this in Power BI, not Power Query

[talk2frank]

note that this does not work if you are outside the US region and using certain datasets - you will still need to create a date from parts

[jpt1228]

@MarcelBeug The cool thing is there are always many ways to solve the problem. I'm not great with code so typically try to use the Query editor.

Thanks for your help!

[jpt1228 · Accepted solution]

Figured it out - Converted the YYYYMM Column to text and then added new column and used this code.

=Date.FromText(
Text.Range([Input Date as Sting], 0,4) & "-"  &
Text.Range([Input Date as Sting], 4,2)
)