Forum Discussion
Date column reconstruction
I modified the query code and it returns the following result :
let
Forrás = Excel.Workbook(File.Contents("C:\Users\XXXXXXX\Downloads\OriginalDateFormat.xlsx"), null, true),
Sheet2 = Forrás{[Name="Sheet1"]}[Data],
#"Típus módosítva" = Table.TransformColumnTypes(Sheet2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Első sorok eltávolítva" = Table.Skip(#"Típus módosítva",1),
#"Előléptetett fejlécek" = Table.PromoteHeaders(#"Első sorok eltávolítva", [PromoteAllScalars=true]),
#"Típus módosítva1" = Table.TransformColumnTypes(#"Előléptetett fejlécek",{{"Row Labels", type text}, {"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}, {"a", Int64.Type}, {"B", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"Grand Total", Int64.Type}}),
#"Érték felülírva" = Table.ReplaceValue(#"Típus módosítva1",null,0,Replacer.ReplaceValue,{"Row Labels", "x", "y", "z", "a", "B", "c", "d", "Grand Total"}),
#"Típus módosítva2" = Table.TransformColumnTypes(#"Érték felülírva",{{"x", Int64.Type}, {"y", Int64.Type}, {"z", Int64.Type}, {"a", Int64.Type}, {"B", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"Grand Total", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Típus módosítva2", "Year", each if Text.Contains([Row Labels], "0") then [Row Labels] else if Text.Contains([Row Labels], "1") then [Row Labels] else if Text.Contains([Row Labels], "2") then [Row Labels] else if Text.Contains([Row Labels], "3") then [Row Labels] else if Text.Contains([Row Labels], "4") then [Row Labels] else if Text.Contains([Row Labels], "5") then [Row Labels] else if Text.Contains([Row Labels], "6") then [Row Labels] else if Text.Contains([Row Labels], "7") then [Row Labels] else if Text.Contains([Row Labels], "8") then [Row Labels] else if Text.Contains([Row Labels], "9") then [Row Labels] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Year"}),
#"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "RemoveYearRows", each if [Row Labels] = [Year] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column1", each ([RemoveYearRows] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RemoveYearRows"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Year. Month", each [Year]&". "&[Row Labels]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Year"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Row Labels", "Year. Month", "x", "y", "z", "a", "B", "c", "d", "Grand Total"})
in
#"Reordered Columns"
Hello BA_Pete,
Your answer would be perfect however after the year 2017. aug the year property is going back to 2015 and stuck in a loop until the last date ( 2021. jun). Sorry for not telling you that there is more date data and just snipped out a part of it.
I don't know how to implement a perfect date column for this type of report that we have because the months are not representing the normal months (1-31 or 1-30). In an example our August month is from August 20 until September 19.
Community ChampionFollowing on from BA_Pete 's code and suggestion, you can use this extended version of his code to include a column with a date:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFWK1YlWKk4tANP5ySVgOi+/DEynpCaDaaBCMzAjKzEPTKelJoHp3MQiMJ1YUATlV0LUleZB6RyIfGk6LotS0W0yp9ymWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [originalColumn = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"originalColumn", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "originalColumn", "dupedColumn"),
#"Replaced Value" = Table.ReplaceValue(#"Duplicated Column", each [dupedColumn], each try Number.From([dupedColumn]) otherwise null,Replacer.ReplaceValue,{"dupedColumn"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "removeRowFlag", each if Text.From([originalColumn]) = Text.From([dupedColumn]) then 1 else 0),
#"Filled Down" = Table.FillDown(#"Added Custom",{"dupedColumn"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([removeRowFlag] = 0)),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows", "newColumn", each Text.Combine({Text.From([dupedColumn], "en-GB"), [originalColumn]}, ". "), type text),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "Custom", each if [originalColumn] = "jan" then 1 else if [originalColumn] = "feb" then 2 else if [originalColumn] = "mar" then 3 else if [originalColumn] = "apr" then 4 else if [originalColumn] = "may" then 5 else if [originalColumn] = "jun" then 6 else if [originalColumn] = "jul" then 7 else if [originalColumn] = "aug" then 8 else if [originalColumn] = "sep" then 9 else if [originalColumn] = "oct" then 10 else if [originalColumn] = "nov" then 11 else if [originalColumn] = "nove" then 11 else 12),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "MonthNum"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns","nove","nov",Replacer.ReplaceText,{"newColumn"}),
#"Inserted Date" = Table.AddColumn(#"Replaced Value1", "Date", each Date.From([newColumn]), type date)
in
#"Inserted Date"
Once you have created this table, you can now create a date table. This date table is customized to what I think are your requirements regarding months (though please check if the turn of years are correct, since January days < 20 are computed as december of the previous year - correct?)
let
MinDataDate = List.Min(#"Original Query"[Date]),
MaxDataDate = List.Max(#"Original Query"[Date]),
#"MaxSalesDate1" = Date.AddDays(MaxDataDate, 1),
DayCount = Duration.Days(Duration.From(MaxSalesDate1 - MinDataDate)),
Source = List.Dates(MinDataDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
#"Inserted Day" = Table.AddColumn(RenamedColumns, "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Day", "Month", each Date.Month([Date]), Int64.Type),
#"Added Conditional Column" = Table.AddColumn(#"Inserted Month", "Custom", each if [Day] < 20 then [Month] -1 else [Month]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] = 0 then 12 else [Custom]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "NewMonth"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Added Conditional Column2" = Table.AddColumn(#"Inserted Year", "NewYear", each if [NewMonth] < 12 then [Year] else if[Month] = 1 then [Year] -1 else [Year]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column2",{"Year", "Month"}),
#"Added Conditional Column3" = Table.AddColumn(#"Removed Columns1", "Custom", each if [NewMonth] = 1 then "Jan" else if [NewMonth] = 2 then "Feb" else if [NewMonth] = 3 then "Mar" else if [NewMonth] = 4 then "Apr" else if [NewMonth] = 5 then "May" else if [NewYear] = 6 then "Jun" else if [NewMonth] = 7 then "July" else if [NewMonth] = 8 then "Aug" else if [NewMonth] = 9 then "Sep" else if [NewMonth] = 10 then "Oct" else if [NewMonth] = 11 then "Nov" else "Dec"),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column3",{{"Custom", "MonthName"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "YearMonth", each [NewYear] *100 + [NewMonth])
in
#"Added Custom"
You can then create a relationship between the corresponding date fields:
Now use the date table fields in your visuals, measures, slicers etc...