Power Query: Split Folder Part 6
7 May 2025
Welcome to our Power Query blog. This week, I fix a problem that occurs if not all file types exist.
I have covered the topic of getting files from a folder in several blogs; the latest series was Excel Files from a Folder Fiddle. In this series, I will look at how I may extract files from a folder, where some of the files require different transformations to others. The folder shown below contains expense data for May 2024, but not all formats are the same:
My task is to transform all the data and append into a single output Table.
In Part 1, I used the ‘From Folder’ connector to extract data from the folder and transformed / filtered the data to ensure I only had the expense data.
I took three [3] reference copies of Folder_Filtered, one for each file type:
In Part 2, I transformed the data in XLSM Files and TXT Files.
In Part 3, I transformed theCSV Files. Currently, there is only one CSV file, but I assume that there could be more when I refresh the data, so I used the ‘Combine Files’ approach.
I appended my data to create a new query Monthly Expense Data and sorted the data in ascending Date order:
In Part 4, I tested the process by changing and adding data.
Last time, I refined the process by using internal Power Query parameters in the Folders_Filtered query.
The values of the parameters were "EXPENSE" and "NDL".
When I clicked OK, the query looked the same, but I was using parameters for the 'Filtered Rows' step:
Now I did promise to move on to getting the parameters from the Excel workbook, but I hit a problem when I was testing different parameter values. Let's look at what happens if there isn't a file of each type, i.e. at least one of XLSM Files, TXT Files or CSV Files returns no rows.
I decide I need to see Mary's files and I change the value of XLSM_String to "MARY".
This is fine for the xlsm file types, but now no CSV Files are selected.
When I look at the Monthly Expense Data query, I clearly have a problem:
Although the error mentions the CSV_Sample_File, I am more concerned with the results in CSV Files as that is the query I am using to create Monthly Expense Data:
There is no error until the step 'Expanded Column1'. Since that column doesn't exist, I get an error. I need to change the M code. I can test if a query contains no rows by using Table.IsEmpty:
Table.IsEmpty(table as table)
This is a logical, so it is either true or false. I also need to enter a null value into the table, so it has a null row for the append to create Monthly Expense Data. To do this, I am using Table.InsertRows:
Table.InsertRows(table as table, offset as number, rows as list) as table
I need to change the M code in the Advanced Editor. I change this section from:
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"CSV_Transform File"(#"CSV_Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Name", type text}, {"Date", type date}, {"expense code", type text}, {"amount", Int64.Type}})
in
#"Changed Type"
to
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
//Only do the next step if data is found
#"Expanded Table Column1" = if Table.IsEmpty(#"Removed Other Columns1") = true then null else Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"CSV_Transform File"(#"CSV_Sample File"))),
//Only do the last step if data is found, otherwise add an empty Transform File column to avoid errors
#"Changed Type" = if Table.IsEmpty(#"Removed Other Columns1") = true then Table.InsertRows(#"Removed Other Columns1",0, { [Transform File = ""] }) else Table.TransformColumnTypes(#"Expanded Table Column1",{{"Name", type text}, {"Date", type date}, {"expense code", type text}, {"amount", Int64.Type}})
in
#"Changed Type"
This now removes the error from this query:
I will need to tidy up the Monthly Expense Data query, as this will now have an extra column Transform File, and a null row.
Since Transform File will only exist if I am missing data in one of the file types, I must select the columns I want to keep and remove other columns. I will also filter on the Date column to remove null values.
I need to apply the same fixes to the queries XLSM Files and TXT Files. Now my solution is more robust, next time I will move on to getting the parameters from the Excel workbook as promised!
Come back next time for more ways to use Power Query!
