Forum Discussion
Inconsistent Number of Columns in source data
Hi Centaur ,
Yes, sounds like you're following correctly.
If you need the column in a table, then you'd do my conditional add step right at the start.
If you want to remove it from a table where it does exist, then you can multi-select every column that is common to each table and ISN'T [Branch Account] then right-click on one of the selected column headers and select 'Remove Other Columns'. This will remove it if it's there but not error if it's not.
I originally assumed that you were using the Combine & Load function within Power Query, but I'm not sure now. If you are/were you could perform either of the above operations on your sample file to either add/remove this column from all inputs prior to combining them.
Pete
Hi Centaur ,
Firstly, remove all the 'Reordered Columns' steps. That fixes 75% of your issue. The columns won't come out of PQ in that order anyway unless you're outputting to Excel. If you are sending to Excel, then delete the first two Reorder Columns steps as it's only the last one that is relevant.
For the Changed Type step, and any other operations you want to do, you can use the Table.HasColumns function to check if the [Branch Account] column exists before trying to perform a function/operation on it.
From MS Docs:
Table.HasColumns(table as table, columns as any) as logical
For example, to add the column into a table without it, you could do a custom step like:
= if Table.HasColumns(previousStepName, "Branch Account") then previousStepName else Table.AddColumn(previousStepName, "Branch Account", each null)
Pete
Hi Pete, thanks. It seems a little complicated for me. I can say that I do not need the [Branch Account] column in every instance. So knowing this, can I simply filter out this column for every time it is in the excel file? I guess the problem is when the column is not there. maybe you code above needs to be inserted at some point probably early in the steps I assume. Am I following correctly?
Hi Centaur ,
Yes, sounds like you're following correctly.
If you need the column in a table, then you'd do my conditional add step right at the start.
If you want to remove it from a table where it does exist, then you can multi-select every column that is common to each table and ISN'T [Branch Account] then right-click on one of the selected column headers and select 'Remove Other Columns'. This will remove it if it's there but not error if it's not.
I originally assumed that you were using the Combine & Load function within Power Query, but I'm not sure now. If you are/were you could perform either of the above operations on your sample file to either add/remove this column from all inputs prior to combining them.
Pete
Excellent. I removed the "other columns" as you stated. I then had to remove in each line where I had the word Branch Account. After doing that I refreshed and tested a file that did not have the Branch Account column and it worked.
thank you for helping me with this. Really appreciate it. I wish you a good day.
