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

     

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

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