Clean up date column which has month and day number in the same column

[davidmeblack]

Hi guys,

First post so bear with me! I'm exporting a csv file from a software in which the date is given to me as Month and then all days of the month underneath, followed by the next month, and all days of that month. (See below)

I'm trying to clean this up so I can a get manageable date column to work with, I've tried fill down, transpose but can't think of anything else through searching the forum - anyone got any ideas?? Thanks very much in advance!

[dilumd]

Hi @davidmeblack

Please try below code,

Do not promote your headers, change the file location below and input your sheet name. refer the pbix file here

let
    Source = Excel.Workbook(File.Contents("Your File Location"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet Name of your File",Kind="Sheet"]}[Data],
    #"Duplicated Column" = Table.DuplicateColumn(Sheet1_Sheet, "Column1", "Column1 - Copy"),
    #"Calculated Text Length" = Table.TransformColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Column1 - Copy", type text}}, "en-US"),{{"Column1 - Copy", Text.Length, Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Calculated Text Length", "Year", each if [#"Column1 - Copy"] = 4 then [Column1] else null ),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Year"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Month", each if [#"Column1 - Copy"] = 3 then [Column1] else null ),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Month"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Filled Down1", "Date", each if [#"Column1 - Copy"] = 1 then [Column1] else if [#"Column1 - Copy"] = 2 then [Column1] else null ),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column2", each ([Date] <> null)),
    #"Added Conditional Column3" = Table.AddColumn(#"Filtered Rows", "MonthNo", each if [Month] = "Jan" then "01" else if [Month] = "Feb" then "02" else if [Month] = "Mar" then "03" else if [Month] = "Apr" then "04" else if [Month] = "May" then "05" else if [Month] = "Jun" then "06" else if [Month] = "Jul" then "07" else if [Month] = "Aug" then "08" else if [Month] = "Sep" then "09" else if [Month] = "Oct" then "10" else if [Month] = "Nov" then "11" else if [Month] = "Dec" then "12" else "00" ),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"MonthNo", type text}, {"Date", type text}, {"Year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date_C", each [MonthNo]&"/"&[Date]&"/"&[Year]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date_C", type date}})
in
    #"Changed Type1"