Forum Discussion
Power Query fails when column does not exist
Alright, makes sense,
And just because I am still brand new to M, would I put that in my existing code (below), or where?
let Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true), PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Salary", type number}, {"Contr.", type number}}) in #"Changed Type"
you can use try ... otherwise, e.g. like this
let
Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true),
PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Salary", type number}}),
Custom1 = try Table.TransformColumnTypes(#"Changed Type",{{"Contr.", type number}) otherwise #"Changed Type"
in
Custom1
Thanks Stachu, kinda worked.
Is there, however, any way to tweak the code so that all columns can be variable?
EDIT: The code above does not seem to actually change the number formatting of "Contr."
there is but it's much more complex. You basically need a predefined list of columns and respective types (step ColumnTypes in the code below), and then you need to compare it with actual column names (you can get them using Table.ColumnNames) - not included in the code below
let TypeTable = #table({"ColumnName", "Type"},{{"Salary", type number},{"Contr.", type number}}), ColumnTypes = Table.AddColumn(TypeTable, "ColType", each {[ColumnName],[Type]}), Source = Excel.Workbook(File.Contents("G:\Desktop\Excel\Testbook.xlsx"), null, true), PFA_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"ID", "Name", "Salary", "Contr."},MissingField.Ignore), #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",ColumnTypes[ColType]) in #"Changed Type"After you filter ColumnTypes[ColType] to only include relevant column names, you're good to go
I think this might actually be the way to go, since I know for certain the 20 columns that CAN exist and in case they do, those are the ones that should always be imported (the issue arises when one of them does not exist). This would be a far more flexible solution as well.
Let's say I have the data below in an Excel tabel in the same workbook (let's call i d_columns). How would I adjust your code below accordingly?
Thanks a lot Stachu
Column NameDate type
Column name Data type ID Whole number Name Text Salary Decimal number Contr. Decimal number Effective date Date Amount Decimal number
it works for me here (2 rows, all valid decimals), are you sure the transformation itself doesn't return any errors with full dataset? e.g. some entries in [Contr.] have text?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzUtJRMtYzVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Salary = _t, #"Contr." = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Salary", type number}}), Custom1 = try Table.TransformColumnTypes(#"Changed Type", {{"Contr.", type number}}) otherwise #"Changed Type" in Custom1