Hello. I need your help. I'm becoming crazy
I've seen plenty of posts online, but nothing worked.
I've been using this file without errors (only changing the source) 4 times, every end of month from January to April.
However, with May something doesn't work.
I don't receive the error within the edit, here 100% is correct.
Only when I click close and load I see the message: [DataFormat.Error] We couldn't convert to Number and after that "download did not complete" below the query on the right.
I tried to change the data type in any, to see many times the code but nothing worked.
let
Source = Folder.Files("G:\A Liquidita' CC Agevolato\conti correnti _ time deposit imprese\2021\21_05"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File (4)", each #"Transform File (4)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
#"Removed Top Rows" = Table.Skip(#"Expanded Table Column1",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Saldi Banca del 03052021.xlsx", type any}, {"C/C (abi-cab-n°conto)", type any}, {"Intestatario", type any}, {"Tipologia C/C", type any}, {"Saldo Liquido del 30/04/2021", type any}, {"Column6", type any}, {"1", Int64.Type}, {"Column8", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column6", "1", "Column8"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Saldi Banca del 03052021.xlsx] <> "Saldi Banca del 03052021.xlsx") and ([#"C/C (abi-cab-n°conto)"] <> null and [#"C/C (abi-cab-n°conto)"] <> "C/C (abi-cab-n°conto)" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 04/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 05/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 06/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 07/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 10/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 11/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 12/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 13/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 14/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 17/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 18/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 19/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 20/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 21/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 24/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 25/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 26/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 27/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 28/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 31/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "Totale ") and ([#"Tipologia C/C"] <> "Conto Tecnico TIME DEPOSIT")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Saldo Liquido del 30/04/2021", "Outstanding"}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Renamed Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Saldi Banca del 03052021.xlsx], " ", ".", 2, 0), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Saldi Banca del 03052021.xlsx"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns1", "Text Between Delimiters", Splitter.SplitTextByPositions({0, 2}, false), {"Text Between Delimiters.1", "Text Between Delimiters.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Text Between Delimiters.1", type text}, {"Text Between Delimiters.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type2", "Text Between Delimiters.2", Splitter.SplitTextByPositions({0, 2}, false), {"Text Between Delimiters.2.1", "Text Between Delimiters.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Text Between Delimiters.2.1", type text}, {"Text Between Delimiters.2.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Data", each Date.FromText([Text Between Delimiters.1]-1 & "/" & [Text Between Delimiters.2.1] & "/" & [Text Between Delimiters.2.2])),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns2",{{"Text Between Delimiters.1", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type4", "Custom", each if [Text Between Delimiters.1] = 3 or [Text Between Delimiters.1] = 10 or [Text Between Delimiters.1] = 17 or [Text Between Delimiters.1] = 24 then [Text Between Delimiters.1] -3 else [Text Between Delimiters.1] -1),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom1",{"Text Between Delimiters.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns3",{"C/C (abi-cab-n°conto)", "Intestatario", "Tipologia C/C", "Outstanding", "Custom", "Text Between Delimiters.2.1", "Text Between Delimiters.2.2"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type5", "Data", each Date.FromText([Custom] & "/" & [Text Between Delimiters.2.1] & "/" &[Text Between Delimiters.2.2])),
#"Removed Columns4" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Text Between Delimiters.2.1", "Text Between Delimiters.2.2"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Removed Columns4",{{"Data", type date}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type6", "Custom", each Text.Replace([#"C/C (abi-cab-n°conto)"]," ","")),
#"Removed Columns5" = Table.RemoveColumns(#"Added Custom3",{"C/C (abi-cab-n°conto)"}),
#"Inserted Last Characters" = Table.AddColumn(#"Removed Columns5", "Last Characters", each Text.End([Custom], 7), type text),
#"Changed Type7" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", Int64.Type}}),
#"Removed Columns6" = Table.RemoveColumns(#"Changed Type7",{"Custom"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns6", each ([#"Tipologia C/C"] <> "Conto Tecnico TIME DEPOSIT")),
#"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows1",{{"Last Characters", "ID"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"ID", "Intestatario", "Tipologia C/C", "Data", "Outstanding"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns1", {"ID"}, #"CC DI Penta", {"cc"}, "CC DI Penta", JoinKind.LeftOuter),
#"Expanded CC DI Penta" = Table.ExpandTableColumn(#"Merged Queries", "CC DI Penta", {"Tasso"}, {"Tasso"}),
#"Reordered Columns2" = Table.ReorderColumns(#"Expanded CC DI Penta",{"ID", "Intestatario", "Tipologia C/C", "Data", "Outstanding", "Tasso"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns2",null,0.0001,Replacer.ReplaceValue,{"Tasso"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Outstanding", type any}, {"Tasso", type any}, {"ID", type any}})
in
#"Changed Type"
100% green, no errors below the columns.
Please help me!!!
I've seen plenty of posts online, but nothing worked.
I've been using this file without errors (only changing the source) 4 times, every end of month from January to April.
However, with May something doesn't work.
I don't receive the error within the edit, here 100% is correct.
Only when I click close and load I see the message: [DataFormat.Error] We couldn't convert to Number and after that "download did not complete" below the query on the right.
I tried to change the data type in any, to see many times the code but nothing worked.
let
Source = Folder.Files("G:\A Liquidita' CC Agevolato\conti correnti _ time deposit imprese\2021\21_05"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File (4)", each #"Transform File (4)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (4)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (4)", Table.ColumnNames(#"Transform File (4)"(#"Sample File (4)"))),
#"Removed Top Rows" = Table.Skip(#"Expanded Table Column1",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Saldi Banca del 03052021.xlsx", type any}, {"C/C (abi-cab-n°conto)", type any}, {"Intestatario", type any}, {"Tipologia C/C", type any}, {"Saldo Liquido del 30/04/2021", type any}, {"Column6", type any}, {"1", Int64.Type}, {"Column8", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column6", "1", "Column8"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Saldi Banca del 03052021.xlsx] <> "Saldi Banca del 03052021.xlsx") and ([#"C/C (abi-cab-n°conto)"] <> null and [#"C/C (abi-cab-n°conto)"] <> "C/C (abi-cab-n°conto)" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 04/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 05/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 06/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 07/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 10/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 11/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 12/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 13/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 14/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 17/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 18/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 19/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 20/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 21/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 24/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 25/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 26/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 27/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 28/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "SALDI C/C BANCA: RILEVAZIONE AL 31/05/2021" and [#"C/C (abi-cab-n°conto)"] <> "Totale ") and ([#"Tipologia C/C"] <> "Conto Tecnico TIME DEPOSIT")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Saldo Liquido del 30/04/2021", "Outstanding"}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Renamed Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Saldi Banca del 03052021.xlsx], " ", ".", 2, 0), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Saldi Banca del 03052021.xlsx"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns1", "Text Between Delimiters", Splitter.SplitTextByPositions({0, 2}, false), {"Text Between Delimiters.1", "Text Between Delimiters.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Text Between Delimiters.1", type text}, {"Text Between Delimiters.2", type text}}),
#"Split Column by Position1" = Table.SplitColumn(#"Changed Type2", "Text Between Delimiters.2", Splitter.SplitTextByPositions({0, 2}, false), {"Text Between Delimiters.2.1", "Text Between Delimiters.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Text Between Delimiters.2.1", type text}, {"Text Between Delimiters.2.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Data", each Date.FromText([Text Between Delimiters.1]-1 & "/" & [Text Between Delimiters.2.1] & "/" & [Text Between Delimiters.2.2])),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Data"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns2",{{"Text Between Delimiters.1", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type4", "Custom", each if [Text Between Delimiters.1] = 3 or [Text Between Delimiters.1] = 10 or [Text Between Delimiters.1] = 17 or [Text Between Delimiters.1] = 24 then [Text Between Delimiters.1] -3 else [Text Between Delimiters.1] -1),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom1",{"Text Between Delimiters.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns3",{"C/C (abi-cab-n°conto)", "Intestatario", "Tipologia C/C", "Outstanding", "Custom", "Text Between Delimiters.2.1", "Text Between Delimiters.2.2"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Reordered Columns",{{"Custom", type text}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type5", "Data", each Date.FromText([Custom] & "/" & [Text Between Delimiters.2.1] & "/" &[Text Between Delimiters.2.2])),
#"Removed Columns4" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Text Between Delimiters.2.1", "Text Between Delimiters.2.2"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Removed Columns4",{{"Data", type date}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type6", "Custom", each Text.Replace([#"C/C (abi-cab-n°conto)"]," ","")),
#"Removed Columns5" = Table.RemoveColumns(#"Added Custom3",{"C/C (abi-cab-n°conto)"}),
#"Inserted Last Characters" = Table.AddColumn(#"Removed Columns5", "Last Characters", each Text.End([Custom], 7), type text),
#"Changed Type7" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", Int64.Type}}),
#"Removed Columns6" = Table.RemoveColumns(#"Changed Type7",{"Custom"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns6", each ([#"Tipologia C/C"] <> "Conto Tecnico TIME DEPOSIT")),
#"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows1",{{"Last Characters", "ID"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns2",{"ID", "Intestatario", "Tipologia C/C", "Data", "Outstanding"}),
#"Merged Queries" = Table.NestedJoin(#"Reordered Columns1", {"ID"}, #"CC DI Penta", {"cc"}, "CC DI Penta", JoinKind.LeftOuter),
#"Expanded CC DI Penta" = Table.ExpandTableColumn(#"Merged Queries", "CC DI Penta", {"Tasso"}, {"Tasso"}),
#"Reordered Columns2" = Table.ReorderColumns(#"Expanded CC DI Penta",{"ID", "Intestatario", "Tipologia C/C", "Data", "Outstanding", "Tasso"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns2",null,0.0001,Replacer.ReplaceValue,{"Tasso"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Outstanding", type any}, {"Tasso", type any}, {"ID", type any}})
in
#"Changed Type"
100% green, no errors below the columns.
Please help me!!!
