Missing Columns Error Power Query

[thedeadzeds]

Hi Guys,

Please can you help with the below. When I import the data sometimes some of the columns will be missing from the original dataset. This is ok because I only need certain column but how do I ingore the missing column error?

Thanks

let
    Source = Excel.Workbook(File.Contents("C:\Users\Export.xlsx"), null, true),
    Export_Sheet = Source{[Item="Export",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Export_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order Date", type datetime}, {"Handover Date", type datetime}, {"Invoice Date", type date}, {"Order No", Int64.Type}, {"Keyloop DMS Magic Number", type text}, {"Dealership", type text}, {"Customer Name", type text}, {"Customer Scheme", type any}, {"Sales Exec", type text}, {"Reg No", type text}, {"Make", type text}, {"Range", type text}, {"Model", type text}, {"Derivative", type text}, {"Vin Number", type any}, {"New/Used", type text}, {"Customer Type", type any}, {"Vehicle Without VAT", type number}, {"Vehicle", Int64.Type}, {"Factory Options", type number}, {"Front End", type any}, {"Front End Margin Adjustment   ", type any}, {"New Car Reg Bonus", type any}, {"Investor fund ", type any}, {"Bonus Box", type any}, {"Del Profit", type any}, {"Service Plan Income", type any}, {"Contract Hire Base", type any}, {"B/E Margin Adjustment ", type any}, {"Retailer", type any}, {"Incentives", Int64.Type}, {"Over Allowance", Int64.Type}, {"Finance Type", type any}, {"Finance Income", type number}, {"Total Profit", type number}, {"Deal Closed", type any}, {"Manufacturer Order No", type number}, {"Quality - Conquest Lead", type any}, {"Quality - Loyalty Lead ", type any}, {"Loyalty - Service Plans ", type any}, {"Volume - RPM", type any}, {"Total Bonus", type number}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Order Date", "Handover Date", "Invoice Date", "Order No", "Keyloop DMS Magic Number", "Dealership", "Customer Name", "Customer Scheme", "Sales Exec", "Reg No", "Make", "Range", "Model", "Derivative", "Vin Number", "New/Used", "Customer Type"})
in
    #"Removed Other Columns"

 

[RoryA]

Do the type changing after your 'remove other columns' step, so that you only name the columns that are actually there.

 

[thedeadzeds]

Thanks, I've tried that but I still get the error message missing columns.

 

[RoryA]

Did you remove the original Changed Type step?

 

[thedeadzeds]

Hi, Yes the query now looks like this but I still get an error is there is a missing column

let
    Source = Excel.Workbook(File.Contents("C:\Users\Export.xlsx"), null, true),
    Export_Sheet = Source{[Item="Export",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Export_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Order Date", "Handover Date", "Invoice Date", "Order No", "Customer Name", "Customer Scheme", "Sales Exec", "Reg No", "Make", "Range", "Model", "Derivative", "Vin Number", "New/Used", "Customer Type"})
in
    #"Removed Other Columns"

 

[RoryA]

It would appear you are trying to select a column that doesn't exist in the source data. I had interpreted your original post as meaning that the columns that might be missing weren't required for your final return data - is that not the case?

 

[thedeadzeds]

It would appear you are trying to select a column that doesn't exist in the source data. I had interpreted your original post as meaning that the columns that might be missing weren't required for your final return data - is that not the case?

Yes this is correct. I've managed to find 'MissingField.UseNull' which works great.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Export.xlsx"), null, true),
    Export_Sheet = Source{[Item="Export",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Export_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Order Date", "Handover Date", "Invoice Date", "Order No", "Customer Name", "Customer Scheme", "Sales Exec", "Reg No", "Make", "Range", "Model", "Derivative", "Vin Number", "New/Used", "Customer Type"}, MissingField.UseNull)
in
    #"Removed Other Columns"