We couldn't parse the input provided as a Date value.

[millhou123]

I have a query that I have been using for a couple months and now I get an error about not being able to parse a date value.
It is the last step when I try to group rows by #Inv and Merged columns. it works perfect when I only include the #INV column but when using merged it does not. I have parsed the date out to d/m/y columns set them as whole numbers and remerged them and formatted as date but this did not fix this error. As far as I can tell there are no extra spaces before or after the data. I have no idea what is causing this and any help is very much appreciated.

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\njm3546\Desktop\Report.txt"), null, null, 1252)}),
    #"Split Column by Position" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByPositions({0, 9}, false),{"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Position1" = Table.SplitColumn(#"Changed Type","Column1.2",Splitter.SplitTextByPositions({0, 11}, false),{"Column1.2.1", "Column1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Split Column by Position2" = Table.SplitColumn(#"Changed Type1","Column1.2.1",Splitter.SplitTextByPositions({0, 11}, false),{"Column1.2.1.1", "Column1.2.1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Column1.2.1.1", type text}, {"Column1.2.1.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Column1.2.1.2"}),
    #"Split Column by Position3" = Table.SplitColumn(#"Removed Columns","Column1.2.2",Splitter.SplitTextByPositions({0, 11}, false),{"Column1.2.2.1", "Column1.2.2.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position3",{{"Column1.2.2.1", type text}, {"Column1.2.2.2", type text}}),
    #"Split Column by Position4" = Table.SplitColumn(#"Changed Type3","Column1.2.2.2",Splitter.SplitTextByPositions({0, 3}, false),{"Column1.2.2.2.1", "Column1.2.2.2.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position4",{{"Column1.2.2.2.1", type text}, {"Column1.2.2.2.2", type text}}),
    #"Split Column by Position5" = Table.SplitColumn(#"Changed Type4","Column1.2.2.2.2",Splitter.SplitTextByPositions({0, 13}, false),{"Column1.2.2.2.2.1", "Column1.2.2.2.2.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Position5",{{"Column1.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2", type text}}),
    #"Split Column by Position6" = Table.SplitColumn(#"Changed Type5","Column1.2.2.2.2.2",Splitter.SplitTextByPositions({0, 8}, false),{"Column1.2.2.2.2.2.1", "Column1.2.2.2.2.2.2"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Position6",{{"Column1.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2", type text}}),
    #"Split Column by Position7" = Table.SplitColumn(#"Changed Type6","Column1.2.2.2.2.2.2",Splitter.SplitTextByPositions({0, 4}, false),{"Column1.2.2.2.2.2.2.1", "Column1.2.2.2.2.2.2.2"}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Position7",{{"Column1.2.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2.2", type text}}),
    #"Split Column by Position8" = Table.SplitColumn(#"Changed Type7","Column1.2.2.2.2.2.2.2",Splitter.SplitTextByPositions({0, 13}, false),{"Column1.2.2.2.2.2.2.2.1", "Column1.2.2.2.2.2.2.2.2"}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Split Column by Position8",{{"Column1.2.2.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2.2.2", type text}}),
    #"Split Column by Position9" = Table.SplitColumn(#"Changed Type8","Column1.2.2.2.2.2.2.2.2",Splitter.SplitTextByPositions({0, 13}, false),{"Column1.2.2.2.2.2.2.2.2.1", "Column1.2.2.2.2.2.2.2.2.2"}),
    #"Changed Type9" = Table.TransformColumnTypes(#"Split Column by Position9",{{"Column1.2.2.2.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2.2.2.2", type text}}),
    #"Split Column by Position10" = Table.SplitColumn(#"Changed Type9","Column1.2.2.2.2.2.2.2.2.2",Splitter.SplitTextByPositions({0, 14}, false),{"Column1.2.2.2.2.2.2.2.2.2.1", "Column1.2.2.2.2.2.2.2.2.2.2"}),
    #"Changed Type10" = Table.TransformColumnTypes(#"Split Column by Position10",{{"Column1.2.2.2.2.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2.2.2.2.2", type text}}),
    #"Split Column by Position11" = Table.SplitColumn(#"Changed Type10","Column1.2.2.2.2.2.2.2.2.2.2",Splitter.SplitTextByPositions({0, 14}, false),{"Column1.2.2.2.2.2.2.2.2.2.2.1", "Column1.2.2.2.2.2.2.2.2.2.2.2"}),
    #"Changed Type11" = Table.TransformColumnTypes(#"Split Column by Position11",{{"Column1.2.2.2.2.2.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2.2.2.2.2.2", type text}}),
    #"Split Column by Position12" = Table.SplitColumn(#"Changed Type11","Column1.2.2.2.2.2.2.2.2.2.2.2",Splitter.SplitTextByPositions({0, 12}, false),{"Column1.2.2.2.2.2.2.2.2.2.2.2.1", "Column1.2.2.2.2.2.2.2.2.2.2.2.2"}),
    #"Changed Type12" = Table.TransformColumnTypes(#"Split Column by Position12",{{"Column1.2.2.2.2.2.2.2.2.2.2.2.1", type text}, {"Column1.2.2.2.2.2.2.2.2.2.2.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type12",{{"Column1.1", "Tran #"}, {"Column1.2.1.1", "Entry Date"}, {"Column1.2.2.1", "Effect Date"}, {"Column1.2.2.2.1", "Tr Cd"}, {"Column1.2.2.2.2.1", "Tran Desc"}}),
    #"Changed Type13" = Table.TransformColumnTypes(#"Renamed Columns",{{"Entry Date", type date}, {"Effect Date", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type13",{{"Column1.2.2.2.2.2.1", "Loan #"}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"Column1.2.2.2.2.2.2.1", "Inv #"}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"Column1.2.2.2.2.2.2.2.1", "Due Date"}, {"Column1.2.2.2.2.2.2.2.2.1", "Tran Amt"}, {"Column1.2.2.2.2.2.2.2.2.2.1", "Principle"}, {"Column1.2.2.2.2.2.2.2.2.2.2.1", "Interest"}, {"Column1.2.2.2.2.2.2.2.2.2.2.2.1", "Sevice Fee"}, {"Column1.2.2.2.2.2.2.2.2.2.2.2.2", "Late Fee"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns3", each ([Due Date] = "" or [Due Date] = "             ") and ([#"Inv #"] <> " GCR" and [#"Inv #"] <> " SIT" and [#"Inv #"] <> " WF ") and ([#"Loan #"] <> "" and [#"Loan #"] <> "        ")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Concat", each Text.From(Text.Trim([#"Inv #"]))&Text.From(Text.Trim([#"Loan #"]))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Clean Tran Amt", each Text.Trim([Tran Amt])),
    #"Changed Type14" = Table.TransformColumnTypes(#"Added Custom1",{{"Clean Tran Amt", type number}, {"Effect Date", type date}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type14", {{"Effect Date", type text}}, "en-US"), "Effect Date", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Effect Date.1", "Effect Date.2", "Effect Date.3"}),
    #"Changed Type15" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Effect Date.1", Int64.Type}, {"Effect Date.2", Int64.Type}, {"Effect Date.3", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type15", {{"Effect Date.1", type text}, {"Effect Date.2", type text}, {"Effect Date.3", type text}}, "en-US"),{"Effect Date.1", "Effect Date.2", "Effect Date.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Changed Type16" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type16", {"Inv #", "Merged"}, {{"Count", each List.Sum([Clean Tran Amt]), type number}})
in
    #"Grouped Rows"

 

[Ken Puls]

Any chance you can upload a small sample workbook that illustrates the issue? It's kind of hard to say without something to look at.