Power Query fails when column does not exist

[Anonymous]

Hi,

I am struggling with a Power Query element.

I have a query linked to a data source (sheet 1 of the Excel workbook "Testbook") that promotes headers, keeps certain columns (about 20), and changes the data type.

The core problem is that the dataset will not always contain all 20 columns (although at least 10 will always exist). For the "Changed Type" part, the code fails when at least 1 column does not exist. For the "Remove Other Columns" part, the "MissingField.Ignore" argument takes care of the issue.

Hence, I am looking for a similar argument on other solution that ignores missing columns.

Later, I'd like to clean up the text in one of the columns (formatted as e.g. "10 01 80-1210") by removing the spaces and dash (end goal would be "1001801210"), but again the query would fail in case of missing columns.

I have enclosed a sample code below (for simplicity, I have used 4 columns instead of 20 columns), but my point is that the code would fail if e.g. the "Contr." column does not exist.)

Thanks a lot in advance, let me know if you need further from me.

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"

[Stachu]

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

[Anonymous]

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."

[Stachu]

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

[Stachu]

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

[Anonymous]

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

[Stachu]

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
    ColumnTypes

[Anonymous · Accepted solution]

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"