Forum Discussion

GGG123's avatar
GGG123
Regular Visitor
2 years ago
Solved

Issue with List.Max ([Date]) in power query

This formula      Date.From(List.Max(Table.Column(#"Reordered Columns", [Date]))) - Date.From([Date])   is returning this error Expression.      Error:    We cannot convert the value #date(202...
Need Help
  • GGG123's avatar
    GGG123
    2 years ago

    I finally worked it out. All I did is to go at the beginning of the code and Filtered Out "Undeposited Items" which was mentioned in the error I was getting 'DataFormat.Error: We couldn't parse the input provided as a Date'.

    The thing that I couldnt understand is that when looking at the Column Profile, everything showed 0 Error and the count seemed to be all in order. 

    Thanks for your help anyway!

GGG123's avatar
GGG123
Regular Visitor
2 years ago

 

Now I'm getting this error:

   DataFormat.Error: We couldn't parse the input provided as a Dat

It's so weird because until last month I never had problems with this Power Query model. 

Also, when I use the formula List.Max to find the latest date on a different paper, it works fine. 

I tried to changed the Date as Locale and play around a bit but still the same error.

  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    Hello @ ,

    If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.


    Do you perhaps have a screenshot of your data table or an excerpt of the data or the M-Code? It looks like your column is not correctly defined as a Date data type. In the following screenshot I have added my solution to some test data.

     


    Best regards from Germany
    Manuel Bolz

    🟦Follow me on LinkedIn
    🟨How to Get Your Question Answered Quickly
    🟩Fabric Community Conference
    🟪My Solutions on Github

    • GGG123's avatar
      GGG123
      Regular Visitor
      2 years ago

      Thnaks for all your answers. However, I ve tried that too and the data seems fine. 

      See below:

       

       

      I also tried to look for the 'Undeposited Items' mentioned in the Error but the column seems to be having only dates, as it should.

      • ManuelBolz's avatar
        ManuelBolz
        Responsive Resident
        2 years ago

        Hello GGG123,

        If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

        Here is my slightly adapted code. I hope this helps. Here you have to adapt the "Source" step to your code again.

         

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc1LDsAgCEXRvTA2kY+KezHufxuFkjY2KZM3ObmwFlCvSJWRGxQgVVsW2OVfWir9FpZTpvhKKtEYtAdYyHZyvNGK/ZXBnmAkeIp6QxmMAPqA39IM2J7sCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Page = _t, Line = _t]),
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}}),

    LatestDate = List.Max(Table.Column(Type, "Date")),
    AddedDateDifference = Table.AddColumn(Type, "Date Difference", each Duration.Days(LatestDate - [Date]), Int64.Type)
in
    AddedDateDifference

         

         

         



        Best regards from Germany
        Manuel Bolz

        🟦Follow me on LinkedIn
        🟨How to Get Your Question Answered Quickly
        🟩Fabric Community Conference
        🟪My Solutions on Github