Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power Query fails when column does not exist

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"
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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"

     

7 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Stachu
        Community Champion
        6 years ago

        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