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

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
Icon for Responsive Resident rankResponsive 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