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"
Hi mmarton ,
In Power Query, create a new blank query and paste the following code over the default code to follow the steps I took to complete this:
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)
in
#"Inserted Merged Column"
Changing the abbreviated months (jan, feb, mar etc.) to the full version of the month names would be significantly more complicated and would probably be best solved by removing the original column as a source requirement altogether and implementing a proper calendar table into your model.
Pete
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.
Following 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...
Hi mmarton ,
Probably best if you can share an Excel file with your full original date column in it, and as full of a required output as possible, as I'm struggling to understand the issue that you are describing.
Using the method I provided it shouldn't matter in what order the years/months are, as long as the correct months directly follow the correct year in each group (if that makes sense?).
Pete
