Forum Discussion
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 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)
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)
)
12 Replies
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?
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"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)
)
I would like to see how to do it in dax when coming from text
Hi,
This calculated column formula will work
Date = 1*("1/"&right(Data[YYYYMM],2)&"/"&LEFT(Data[YYYYMM],4))
Hope this helps.
