CSV files with only MM/DD available in a 365 day duration, how do I add the correct year?

[steelsparky]

Hello, beginning user here. I have CSV files that I "autopoll" from a industrial controller data base, and unfortuntely it does not include a year. In some cases, I have a 365day log, at 1hr intervals in the file which looks like this:

Log Time LIQUID LEVEL-1 - CONTROL VALUE
13:00:00 Sat 10/20 0
14:00:00 Sat 10/20 0
15:00:00 Sat 10/20 0
16:00:00 Sat 10/20 0
17:00:00 Sat 10/20 0
18:00:00 Sat 10/20 0
19:00:00 Sat 10/20 0
20:00:00 Sat 10/20 0
21:00:00 Sat 10/20 19.20
22:00:00 Sat 10/20 0
23:00:00 Sat 10/20 0
00:00:00 Sun 10/21 0
01:00:00 Sun 10/21 0
02:00:01 Sun 10/21 6.80
03:00:00 Sun 10/21 0
04:00:00 Sun 10/21 0
05:00:00 Sun 10/21 20.60
06:00:00 Sun 10/21 0
07:00:00 Sun 10/21 0.60

So far I have been able to replicate a visualiation of this data the same as if infront of this controller, but only by referencing a rolling index 1-xxxx depending on the number of my rows in my axis. The issue I have is that I cannot opt to add a year on my controllers software, and when I manipulate the data to a column of only "OCT 20", when I try to formart from text to date, I end up with OCT 20 2019. The issue I have is since this is 365 days of data, I end up with duplicate dates, and my visualization is not correct. I have been looking into DAX but it has become difficult to find an exact solution. I have to manually autopoll this data and this is not always at the same intervals, so I am not sure using the current

[steelsparky]

so I am not sure using the currentday function will keep my dates correct. 

Any help would be appreciated. Thank you for your time. 

[dax]

Hi steelsparky,

You could modify it in Edit Queries. when you convert 10/20 to date type by M code, it will automatically transform to 2019/10/20. Then you could apply measure or calculation on it. 

let
    Source = Csv.Document(File.Contents("C:\Users\(username)\Desktop\csv1.csv"),null,{0, 9},null,1252),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Log Time ", type time}, {"LIQUID LEVEL-1 - CONTROL VALUE", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "LIQUID LEVEL-1 - CONTROL VALUE", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"LIQUID LEVEL-1 - CONTROL VALUE.1", "LIQUID LEVEL-1 - CONTROL VALUE.2", "LIQUID LEVEL-1 - CONTROL VALUE.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"LIQUID LEVEL-1 - CONTROL VALUE.1", type text}, {"LIQUID LEVEL-1 - CONTROL VALUE.2", type date}, {"LIQUID LEVEL-1 - CONTROL VALUE.3", type number}})
in
    #"Changed Type1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[steelsparky]

Thank you for your reply. I've be able to format to a date and split my columns but the problem I'm having is that my top and bottom row in my date column are both 10/20. I'm looking for the top row to begin at 10/20/2018 and then on Jan 1, change to 2019 for the remainder of the rows.

[steelsparky]

Also, I should have mentioned, I have over 8000rows. My example did not reflect that.