PQ - Combine From Folder error

[mdbrierley]

Hi All,

Thanks for taking the time to read.

I'm getting an error related to combining multiple CSV files from a folder in excel.

Steps below from the advanced editor, if this helps:


let
Source = Folder.Files("M:\Reporting & MI\NAH Co\MI\INSTRUCTIONS AND REPORTS\Report Data\Instructions"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Combined Binaries" = Binary.Combine(#"Filtered Rows"[Content]),
#"Imported CSV" = Csv.Document(#"Combined Binaries",null,"#(tab)",null,1200),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV"),
#"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each [FileId] <> "caseid"),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows2",{{"FileId", Int64.Type}, {"InstructionDate", type date}, {"Solicitor", type text}, {"WkSource", type text}, {"NorthSouth", type text}, {"Panel", type text}, {"PanelManager", type text}, {"Instr_Rep", type text}, {"CaseTypeName", type text}, {"IsPPlus", type text}, {"CaseSource", type text}, {"SolicitorRef", type text}, {"FFM_MedCo", type text}, {"MedCoCaseId", type text}, {"FFM_PostApril", type text}, {"", type text}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"FileId"})
in
#"Removed Errors"


So
- I'm picking the folder from which to get my data, which currently has just two CSV files in it, but will expand each week.
- Filtering the list of files within the folder to make sure it only uses CSV files
- Combining the files to get one single set of data
- Making a few formatting changes afterwards.

The problem is that only ever brings back one of the files (the first one) and the data from the second isnt there.
What I've noticed though, is that once the CSV's have been imported, on the first column of the last row, there is a cell with Chinese (I think) symbols in it.
All my languages are set to English and if I extract each CSV one at a time as it's own query, the data looks fine. That wont work for me though as I need this to be a single data set that continues to expand as each CSV is added.
Both CSV's have identical header rows/names.

Does anyone have any ideas? It's driving me crazy!

Thanks so much,
Matt

 

[RoryA]

I'm not sure how you got that code, but it won't work for what you are doing. You actually want a separate function to convert each CSV file to a table that you can then combine. If you use the PQ editor, clicking the button at the top right of the Content column prior to the Binary.Combine step should create what you need.

 

[mdbrierley]

I'm not sure how you got that code, but it won't work for what you are doing. You actually want a separate function to convert each CSV file to a table that you can then combine. If you use the PQ editor, clicking the button at the top right of the Content column prior to the Binary.Combine step should create what you need.

Thank you, bit of a late reply as I've had to switch to another project before returning to this one, but I'll give that a try.