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!

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

GGG123's avatar
GGG123
Regular Visitor
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!