Forum Discussion
Convert YYYYMM to date
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)
)
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)
)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"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!
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
I need to do this in Power BI, not Power Query
I need to do this in Power BI, not Power Query
