Forum Discussion
Date table with no date column
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 |
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
4 Replies
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
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 columnPA
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