Forum Discussion
Date table with no date column
- 5 years ago
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
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
- Anonymous5 years agoNot applicable
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
- mahoneypat5 years ago
Microsoft Employee
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
- Anonymous5 years agoNot applicable
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.