Web Query Not Working

Question

I've been trying to help out a user who is having issues running a web query from within an Excel spreadsheet. Whenever the query is attempted to run, it gives the error "[Expression.Error] The column 'Row Type' of the table wasn't found."

If you go into the code and try to delete {"Row Type", type text} out of the editor, it will move to the next column and declare whatever is next wasn't found as well.

I tried deleting the applied steps, but it deletes that data within the spreadsheet. The person who uses this spreadsheet only knows what it does so I'm not able to pick his brain to figure out how it works or ask about the code that has been put into it. I tried working with Microsoft but they can't work on it because of the custom code that was put into it. Also, The "Row Type" isn't even used in the spreadsheet. The entire column is blank. Any ideas on how to resolve it?

Answer 1

Hi Kris,

Greetings! Thank you for posting to Microsoft Community.

Try remove the steps of "change type" and reload your query.

Then you can delete the column "Row Type".

Best Regards,

Snow Lu

Answer 2

I tried that but it deletes all of the data that they put into the 2 spreadsheet pages in the book. I guess I should've said that I already tried this.

Answer 3

Could you share your query code from advanced editor?

let

Source = Excel.Workbook(File.Contents("C:\Users\Name\Desktop\power query.xlsx"), null, true), 

Sheet1\_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data] 

in

Sheet1\_Sheet

Answer 4

let Source = Web.Page(Web.Contents("https://searchads.google.com/ds/reports/download?ay=20700000001268961&av=21700000001631218&rid=325879&of=webqueryphtml&authuser=0")), Data0 = Source{0}[Data], #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Row Type", type text}, {"From", type date}, {"Effective c:Site.ID", type text}, {"Effective c:Type.ID", type text}, {"Cost", type number}, {"Revenue", type number}}) in #"Changed Type"

Answer 5

It looks the data source is not match your data which is on your screenshot？

let Source = Web.Page(Web.Contents("https://searchads.google.com/ds/reports/download?ay=20700000001268961&av=21700000001631218&rid=325879&of=webqueryphtml&authuser=0")), Data0 = Source{0}[Data], #"Expanded Children" = Table.ExpandTableColumn(Data0, "Children", {"Kind", "Name", "Children", "Text"}, {"Children.Kind", "Children.Name", "Children.Children", "Children.Text"}) in #"Expanded Children"