HI Bill
We have an issue i have 341 rows of data when i add your code it works BUT its goes to 116,000 rows massive duplication????
let
DaleLaptop = "D:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\Amex Statement",
DalePC1 = "G:\OneDrive\Documents\Excel Files\Anditsgone Excell and CSV Files\CSV Files\Amex Statement",
LaptopTest = try List.Count(Folder.Files(DaleLaptop)[Content]),
Desktop1Test = try List.Count(Folder.Files(DalePC1)[Content]),
Source = if LaptopTest[HasError] then
if Desktop1Test[HasError] then
"Cannot find Data Source"
else
Folder.Files(DalePC1)
else
Folder.Files(DaleLaptop),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from Amex Statement", each #"Transform File from Amex Statement"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Amex Statement"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Amex Statement", Table.ColumnNames(#"Transform File from Amex Statement"(#"Sample File (4)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type date}, {"Column2", type text}, {"Column3", type number}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Column2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Column3", "Price"}, {"Column4", "Payee"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Column6", "Column5"}),
#"Capitalized Each Word" = Table.TransformColumns(#"Removed Columns1",{{"Payee", Text.Proper, type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Capitalized Each Word",{{"Price", Currency.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","Uk","UK",Replacer.ReplaceText,{"Payee"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Table.AddColumn(#"Replaced Value", "Period", each if Date.Day([Date]) >= 20 then "20 "& Date.ToText([Date],"MMM","en-US") & " - 19 " & Date.ToText([Date]+#duration(30,0,0,0), "MMM", "en-US") else "20 "& Date.ToText([Date]-#duration(30,0,0,0),"MMM","en-US") & " - 19 " & Date.ToText([Date], "MMM", "en-US") , type text )),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Period"}, {"Period"})
in
#"Expanded Custom"