Power Query error catching when column in source file does not exist

Hi naveen73 ,

Can you tell me if the column names of these columns, which can sometimes be empty or disappear, are fixed? And is the data type of the column fixed? If the above conditions are met, then perhaps you can try the Power Query “try..... .otherwise......” function.

For example:
I have such an excel:

We assume that the Population column is likely to be empty or disappear.

Normal connection:
M code in the Advanced Editor:

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-junyantao\Desktop\test.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"City", type text}, {"Rank", Int64.Type}, {"State", type text}, {"Latitude", type number}, {"Longitude", type number}, {"Population", Int64.Type}})
in
    #"Changed Type"

At this point I removed the Population column from excel and then refreshed it in Power Query:

It'll report an error.

But if I change the M code as below:

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-junyantao\Desktop\test.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = try Table.TransformColumnTypes(#"Promoted Headers",{{"City", type text}, {"Rank", Int64.Type}, {"State", type text}, {"Population", Int64.Type}, {"Latitude", type number}, {"Longitude", type number}}) otherwise Table.TransformColumnTypes(#"Promoted Headers",{{"City", type text}, {"Rank", Int64.Type}, {"State", type text}, {"Latitude", type number}, {"Longitude", type number}})
in
    #"Changed Type"

I changed here using "try......otherwise......":

Then it will work without error:

But this is only used in the case that the column names of your particular columns are fixed, if there are empty or disappearing columns that are completely random, or the name structure is changing, then sorry there may not be a way to achieve your needs.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.