Inconsistent Number of Columns in source data

[Centaur]

Hello,

I am using PQ to consolidated files in a folder. 

It works great. 

The issue is that sometimes the files I receive are missing a column ([Branch Account]). 

I have to add that column prior to refreshing data or else I get an error. 

How could I avoid this error?

I am not sure if this is possible since I understand the number of columns must be consistent. 

here is my advanced editor code:

Sorry its is long but I think I need to provide in entirety. 

I am a novice user. 

let
Source = Folder.Files("C:\Users\XX\Box\Financing\Project Finance\DRAWS\Liquidity Reserve\FBL1N Pmts"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Custom.Data", "Custom.Name", "Name"}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom.Data", [PromoteAllScalars=true]),
#"Renamed Columns1" = Table.RenameColumns(#"Promoted Headers",{{"Copy of 922A-E FBL1N 1.12.22_PJM.xlsx", "File Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Company Code", type text}, {"Document Number", type text}, {"Pay Yes/No", type text}, {"Vendor", type any}, {"Name", type text}, {"Branch account", type text}, {"Payee/er", type text}, {"Document Date", type any}, {"Posting Date", type any}, {"Net due date", type any}, {"Clearing Document", type text}, {"Clearing date", type text}, {"Text", type text}, {"Reference", type any}, {"Amount in doc. curr.", type any}, {"Payment Method", type text}, {"Column17", type text}, {"Part.bank type", type text}, {"Payment Block", type text}, {"Terms of Payment", type text}, {"Sheet1", type text}, {"File Name", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Company Code] <> "Company Code")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows1","Copy of 922A-E FBL1N","",Replacer.ReplaceText,{"File Name"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","_PJM","",Replacer.ReplaceText,{"File Name"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".xlsx","",Replacer.ReplaceText,{"File Name"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value2", "File Name", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"File Name.1", "File Name.2", "File Name.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"File Name.1", Int64.Type}, {"File Name.2", Int64.Type}, {"File Name.3", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Date Of Proposal", each #date([File Name.3],[File Name.1],[File Name.2])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"File Name.1", "File Name.2", "File Name.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Date Of Proposal", type date}, {"Amount in doc. curr.", Currency.Type}, {"Document Date", type date}, {"Posting Date", type date}, {"Net due date", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Date Of Proposal", "Company Code", "Document Number", "Pay Yes/No", "Vendor", "Name", "Branch account", "Payee/er", "Document Date", "Posting Date", "Net due date", "Clearing Document", "Clearing date", "Text", "Reference", "Amount in doc. curr.", "Payment Method", "Column17", "Part.bank type", "Payment Block", "Terms of Payment", "Sheet1"}),
#"Filtered Rows2" = Table.SelectRows(#"Reordered Columns", each true),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows2",{{"Date Of Proposal", type text}}),
#"Replaced Value3" = Table.ReplaceValue(#"Changed Type3","00","20",Replacer.ReplaceText,{"Date Of Proposal"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Replaced Value3",{{"Date Of Proposal", type date}}),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type4", each ([#"Pay Yes/No"] <> "No")),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows3",{"Date Of Proposal", "Company Code", "Pay Yes/No", "Reference", "Amount in doc. curr.", "Vendor", "Name", "Document Date", "Posting Date", "Net due date", "Branch account", "Payee/er", "Clearing Document", "Document Number", "Clearing date", "Text", "Payment Method", "Column17", "Part.bank type", "Payment Block", "Terms of Payment", "Sheet1"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Vendor", Int64.Type}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Changed Type5",{"Date Of Proposal", "Company Code", "Pay Yes/No", "Reference", "Amount in doc. curr.", "Name", "Document Date", "Net due date", "Posting Date", "Vendor", "Branch account", "Payee/er", "Clearing Document", "Document Number", "Clearing date", "Text", "Payment Method", "Column17", "Part.bank type", "Payment Block", "Terms of Payment", "Sheet1"})
in
#"Reordered Columns2"

[BA_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

[Centaur]

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?  

[BA_Pete · Accepted solution]

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

[Centaur]

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.