Expression.Error: The column 'Column1' of the table wasn't found-CodePudding

In power query I am using the below source to find dividend information for Canadian stocks. If I enter ENS it works. If I enter MSFT it defers to Try...Otherwise and evaluates correctly to "NA". If I enter HCA it doesn't work and I get the following error, "Expression.Error: The column 'Column1' of the table wasn't found."

Thanks in advance for any help.

(Ticker as text) as table =>

let  Source = Web.Page(Web.Contents("https://www.ca.dividendinvestor.com/?symbol="&Ticker)),

Data = try Source{1}[Data] otherwise #table({"Column1", "Column2"}, {{"Dividend Yield:","NA"}}),

#"Changed Type" = Table.TransformColumnTypes(Data,{{"Column1", type text}, {"Column2", type text}}),

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "Dividend Yield:")) in

#"Filtered Rows"

CodePudding user response：

You cant see what is happening when you use the code as a function. Change your code to be:

let Ticker="ENS",
Source = Web.Page(Web.Contents("https://www.ca.dividendinvestor.com/?symbol="&Ticker)),
Data = try Source{1}[Data] otherwise #table({"Column1", "Column2"}, {{"Dividend Yield:","NA"}}),
#"Changed Type" = Table.TransformColumnTypes(Data,{{"Column1", type text}, {"Column2", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] = "Dividend Yield:")) in
#"Filtered Rows"

then step through it

When the ticker is ENS, the first step returns this, which has more then one row, so Source{1} which is the 2nd row works perfectly

When the ticker is MSFT, the first step returns this, which has a single row, so the Source{1} errors out [since there is no 2nd row] and goes to the other part

When ticker is HCA you return the results of the table in the Data column on the 2nd row. If you click on it an look at that table, it does not have a column1 or column2 which is what you are referring to in the next step, #"Changed Type", so that errors out. It only has kind, name, children and text

If you need to use the columns regardless of title, then use Table.ColumnNames() to pull the dynamic name. Below code that pulls the dynamic name of the first column for use in filtering

let Ticker="HCA",
Source = Web.Page(Web.Contents("https://www.ca.dividendinvestor.com/?symbol="&Ticker)),
Data = try Source{1}[Data] otherwise #table({"Column1", "Column2"}, {{"NA","NA"}}),
#"Filtered Rows" = Table.SelectRows(Data, each (
    Record.Field(_, Table.ColumnNames(Data){0})
    = "Element")) in
#"Filtered Rows"

CodePudding user response：

Thanks for the advice above about analyzing the steps. You have guided me towards the correct path. It's my first week using powerquery and I'm learning a lot.

I first used the below query:

(Ticker as text) as table => let Source = Web.Page(Web.Contents("https://www.ca.dividendinvestor.com/?symbol="&Ticker)), Data = try Source{1}[Data] otherwise #table({"Column1", "Column2"}, {{"Kind:","NA"}}) in Data

I then added a second query below to filter the rows I needed:

let
Source = Excel.CurrentWorkbook(){[Name="tblCompanySymbols"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"company ticker symbol", type text}}),

#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fxGetDividendCA", each fxGetDividendCA([company ticker symbol])),

#"Expanded fxGetDividendCA" = Table.ExpandTableColumn(#"Invoked Custom Function", "fxGetDividendCA", {"Column1", "Column2"}, {"fxGetDividendCA.Column1", "fxGetDividendCA.Column2"}),

#"Filtered Rows" = Table.SelectRows(#"Expanded fxGetDividendCA", each ([fxGetDividendCA.Column1] = "Dividend Yield:")) in #"Filtered Rows"