Forum Discussion
Issue with List.Max ([Date]) in power query
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!
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
Im not sure how to fit your code into mine.
Here's mine.
let
Source = Excel.Workbook(File.Contents("\\accnzrep01\B2B\Bank\Bank Reconciliation Report\Bank Reconciliation Report Data Export (Month End).xls"), null, true),
Sheet2 = Source{[Name="Sheet1"]}[Data],
#"Removed Top Rows" = Table.Skip(Sheet2,4),
#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Txn Type", each if [Column1] = "Unreconciled Statement Items" then "Unreconciled Statement Items" else if [Column1] = "Unreconciled GL Items" then "Unreconciled GL Items" else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "GL String", each if [Column1] = "Code:" then [Column2] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column1",{"Txn Type", "GL String"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> null and [Column1] <> "Date" and [Column1] <> "Code:" and [Column1] <> "Unreconciled Statement Items" and [Column1] <> "Unreconciled GL Items" and [Column1] <> "BankRecRep.rpt"),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column3", "Page"}, {"Column6", "Line"}, {"Column10", "Type"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column1", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}, {"Column12", "Reference"}, {"Column15", "Details"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Column23", Currency.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Date", "Page", "Line", "Type", "Reference", "Details", "Column23", "Txn Type", "GL String"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns",{{"Column23", "Amount"}}),
#"Uppercased Text" = Table.TransformColumns(#"Renamed Columns2",{{"Reference", Text.Upper, type text}, {"Details", Text.Upper, type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Uppercased Text",{"Txn Type", "GL String", "Date", "Page", "Line", "Type", "Reference", "Details", "Amount"})
in
#"Reordered Columns"
Up until this point everything works fine. Are you able to fit your code into mine so that I only need to paste it in the advanced editor?
Thanks!