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

[naveen73]

Hi All,

I am working with Power Query and importing data from an Excel file, which is a different one every day as this file gets exported every day from a system.

Sometimes there is a column, which I need but when the column is empty in the system, the column does not appear in the Excel file of that day. When the column is missing, Power Query will still be looking for it and will give an error message.

Is there a way that I can say something like, if column header/column is not in file then skip this step or any other way to catch error?

Thanks,

Naveen

[Anonymous · Accepted solution]

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.

[sergievs]

 

you can use in some functions "missingfield.ignore/.usenull/.error, it is usefull in "selectcolumns"