Date column reconstruction

[mmarton]

Hello everyone,

Already tried a lot of thing but I'm not that experinced yet with Power Query.

Does anyone have any advice?

I have a Column in the format of the left one and want to achieve the right one:

HaveWant

[mmarton]

Hello everyone,

@AntoineTRICHET @BA_Pete @PaulDBrown 

Here is an example file of the original data.
[Removed Link]

Sorry but I'm unable to directly attach the files.

[AntoineTRICHET · Accepted solution]

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"

[mmarton]

@AntoineTRICHET  Thank you! 🙂  Perfect output.

@BA_Pete @PaulDBrown Also thank you for your time and help 🙂

[BA_Pete]

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

[mmarton]

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. 

[BA_Pete]

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

[PaulDBrown]

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...

[AntoineTRICHET]

Hi @mmarton 

In Power Query Editor you can :
- add a conditionnal colum taking only the years ;
- fill down this column ;
- remove the lines where your original column contains numerical values using the second conditionnal column you created;
- finally add a custom column which concatenates the year & the month.

Find below my code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNFWK1YlWykrMA9NpqUlgOjexCEznJ5eAaaBCM6wKU1KTYQrMUSQSCyAmFKcWoJiUl1+G0BgLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Added Conditional Column" = Table.AddColumn(Source, "Conditionnal1", each if Text.Contains([Column1], "0") then [Column1] else if Text.Contains([Column1], "1") then [Column1] else if Text.Contains([Column1], "2") then [Column1] else if Text.Contains([Column1], "3") then [Column1] else if Text.Contains([Column1], "4") then [Column1] else if Text.Contains([Column1], "5") then [Column1] else if Text.Contains([Column1], "6") then [Column1] else if Text.Contains([Column1], "7") then [Column1] else if Text.Contains([Column1], "8") then [Column1] else if Text.Contains([Column1], "9") then [Column1] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Conditionnal2", each if Text.Contains([Column1], "0") then 1 else if Text.Contains([Column1], "1") then 1 else if Text.Contains([Column1], "2") then 1 else if Text.Contains([Column1], "3") then 1 else if Text.Contains([Column1], "4") then 1 else if Text.Contains([Column1], "5") then 1 else if Text.Contains([Column1], "6") then 1 else if Text.Contains([Column1], "7") then 1 else if Text.Contains([Column1], "8") then 1 else if Text.Contains([Column1], "9") then 1 else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Conditionnal1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Conditionnal2] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Conditionnal2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Year&Month", each [Conditionnal1]&"-"&[Column1])
in
#"Added Custom"

And the result I get :

Please accept the answer if it meets your need

[mmarton]

Hello @AntoineTRICHET,

I tried your method but got the same result what you sent in with the picture (how surprising). However if you check the output, the date months are repeating and also not reaching the year 2021.
Here is a pic of the output with your code:

The original badly formated column dates are in order but just in wrong format.

[AntoineTRICHET]

Hello @mmarton 
I am not sure I understood. If you have duplicates you can remove duplicates from the final column.
That is normal if you don't have 2021 in your input to not have it in your final column. If you want to have exactly your result with the format you presented in your first post :

- Introduce a new step (as second last step, between Removed Columns & Added Custom steps) where you replace the value in the column1 : jan by January, feb by February...and so on ;

- Replace the last step with this one : #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year&Month", each [Conditionnal1]&". "&[Column1])

[mmarton]

Hello @AntoineTRICHET,

What I meant is if you check the result that the query gives the first issue is it starts with 2015.jan when the first date in the table is 2015.sept
Also if you check the months they are missing, from 2016 until 2021 every year has data from january until december but the table gave back jan-feb-dec for every 2016 year apparence even when the year is alredy 2018 or later.  In 2017 the dates are feb-apr-sep-oct-nov-dec all the other months are missing and overwrited by the earlier mentioned dates. It doesn't reach 2018 just repeats between 2015 and 2017 with the missing months.

Marked the repeating sequence.

[AntoineTRICHET]

Hi @mmarton 
Can you provide an Excel export with your input table and your Power BI file ? It will be easier to understand