Date table with no date column

[Anonymous]

Hi, 

I have a data where i have just Period column with short month (Jan, Feb, Mar, Apr.......,Dec). Rest all the column in numbers with yearly sales numbers. 

I want to convert the Period column into date column. If not possible then how do it make date able or any workaround for long term use. 

The period column is text column and sales are in Decimal numbers. 

@smpa01 @mahoneypat 

Period	Sales 21	Sales 20	Sales 19	Region
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep

[mahoneypat]

Here is one way to do it with your example table structure.  It involves unpivoting your data, splitting the column with the year info and creating a new Date column.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtysxPUdJRCk7MSS1WMDJEMA3gTENLpVidaCWvxDygkCkQm4FpkJhbahKQbQIVNwGL+SYWQfmmcHWOBUVQdWZgDFFXiaHOqzQPScwEKpaDpBci5liajmQvRG9wagGSXqAdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, #"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type text}, {"Sales 21", Int64.Type}, {"Sales 20", Int64.Type}, {"Sales 19", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Period"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.2", "Year"}, {"Value", "Sales"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Date", each Date.FromText("20"&[Year]&"-"&[Period]&"-1")),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Year"})
in
    #"Removed Columns1"

Pat

[Anonymous]

Hi @mahoneypat  

I tried this solution before posting the question but i have more than 20 columns with thousands of rows(didn't mentioned it earlier). 

Want to explore if the Month column could be covnverted into Date type and sales yearly column could be used with that column 

PA

[mahoneypat · Accepted solution]

Can you clarify further?  I thought that is what was provided.  Can you show what the output table should look like?  Your data should be unpivoted for easiest analysis/visualization, and you will need a year component to convert the Period column to a date.

Pat

[Anonymous]

Hi @mahoneypat 

I was not able to access this thread to repsond to your suggestion. 

It's working now. I made date table and then  put a date column as suggested to get the issue resolved. 

Thanks for the help.