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"
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 I don't think you can load it from Excel, I think you need to create in M directly. The reason is, in Excel you cannot store M `type` values, at most you can store a `text` value which has the same name as type does. You can recreate your table from Excel in M, using the code below, you obviously would need to add more entries like {"ID", Int64.Type} to include all
let TypeTable = #table({"ColumnName", "Type"},{{"ID", Int64.Type},{"Name", type text},{"Salary", type number},{"Contr.", type number}}), ColumnTypes = Table.AddColumn(TypeTable, "ColType", each {[ColumnName],[Type]}) in ColumnTypesAlright, 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"