Could not find solutions to column of table not found & loading blocked by failures with other query

[EidelweisLC]

Hi everyone!

I have a stock trade/financial document on google sheets that I would like to bring over to Power Bi to visualise, and this will include live data. The idea is that the user will make updates to his stocks in sheets and he can refresh the visuals on PBI anytime. 

On sheets - 13 tabs in total, 3 tabs that contain information that was web scrapped, and refreshed whenever the ticker changes. 

I imported over to PBID by web and have already cleaned up the tables accordingly in query editor. All was good, applied and saved (No errors on query).

Once I got to creating the visuals and after saving the file, the errors as above starts coming when I hit the refresh button on the visuals page.

p.s These are all dummy data.

Issue 1: The column 'Diluted Net EPS' of the table wasn't found

Query for the particular table that's having issues

let
    Source = Web.BrowserContents("hiddengooglespreadsheetpubhtml"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(4)"}, {"Column5", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(5)"}, {"Column6", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(6)"}, {"Column7", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(7)"}, {"Column8", "DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR > :nth-child(8)"}}, [RowSelector="DIV[id='643174701'] > DIV.ritz.grid-container > TABLE.waffle > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Top Rows" = Table.Skip(#"Removed Blank Rows",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Diluted Net EPS", type number}, {"Gross Profit", type number}, {"Income After Depreciation & Amortization", type number}, {"Net Income", type number}, {"Y / Y Annual Net Income Change", Percentage.Type}, {"Sales", type number}})
in
    #"Changed Type1"

The table on googlesheet.

- A2 to G7 contains all importhtml data. Importhtml functions in row 2.

- The rows numbers are fixed, titles (row 2) are fixed and data is refreshed only on the existing table. No new columns/rows will be automatically added. 

Issue 2: Loading blocked by failures with other queries

All the tables will show this error when refreshed on the main page.

I've been doing alot of reading and trying out the various solutions on probably every source I could find but I'm still getting these errors. Would be great if I could understand these errors, the source of the issues and whether it's related to the importhtml/autorefresh functions in googlesheets, and what I should do if I recreate this project again.

I'm not sure where I've gone wrong really.

Apologies for the lengthy Q, wanted to be as precise as possible. Thank you in advance!

[v-yingjl]

Hi @EidelweisLC ,

For Issue 1:

Seems that the column has already been in the table in power query based on the picture that you have posted.

For Issue 2:

You can try to create a new blank query to use the previous query code to check it again or clear the data source permissions to reconnect to the data source.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EidelweisLC]

Hi YingJie,

thank you for your reply.

For issue 1, yes the table is already in the query but when I attempt to apply, save, or refresh it, I get the error: Expression.Error: The column 'Diluted Net EPS' of the table wasn't found.
Details:
Diluted Net EPS

The table was never deleted or removed from any of the sources, be it power Query or Google Sheets but I still get this notification. I just tried again today and still the same issue. I have checked my data source as well which is what it was supposed to be. Specifically, this whole table had actually been imported into my google sheets document via importhtml method from the web, then I imported this sheet into power BI, so could the IMPORTHTML function be a contributing factor to this issue?

Recently, I had also tried doing web scraping directly from the web into Power BI, and I still get the same issue again.

For issue 2, I had just tried your method (both create a new query from current code and cleared data permissions. I get the same issue: Loading blocked by failures with other queries.

[Anonymous]

I would also Table.TransformColumnNames (or columns), using Text.Clean. Always a good idea anytime something came from the web.

--Nate

[EidelweisLC]

Hi Nate,

thanks for the advice! I have included the text.clean as well for all my tabs that are coming from the web. but I still get the same 2 issues.

[Anonymous]

Have you tried clearing the cache in the Power BI Desktop options?

[EidelweisLC]

Yes I have done that as well. It still didn't work unfortunately.