Forum Discussion

jpt1228's avatar
jpt1228
Responsive Resident
8 years ago
Solved

Convert YYYYMM to date

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 c...
Need Help
  • jpt1228's avatar
    jpt1228
    8 years ago

    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)
    )

     

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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"

 

jpt1228's avatar
jpt1228
Responsive Resident
8 years ago

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!