Error referencing contents of one query within another query

[Anonymous]

Currently, I'm pulling the contents of an excel file (Baker Hughes rig count data) by referening the URL of the file.  The problem is that the href url changes every Friday at noon CT.  Thus, I've been editing the source by copy/pasting the new URL, which is more manual than I would like.  

Here is Baker Hughes Rig Count the webpage: http://phx.corporate-ir.net/phoenix.zhtml?c=79687&p=irol-reportsother

I'm pull the 2nd file address titled: North America Rotary Rig Count Pivot Table (Feb 2011 - Current)

In google sheets, I'm pulling the appropriate href URL, which my first Power BI query pulls, thus I bring in the appropriate URL to use.

let
    Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vS82VudtPYLG51JThRAEMB_b4AaYkApzTbVU8S3OzeM6_dq0r64SagRcmyfG7crGznBDB8ZzvEI1ld5/pub?gid=0&single=true&output=csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",51),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "URL"}}),
    #"Duplicated Column" = Table.AddColumn(#"Renamed Columns", "URL - Copy", each [URL], type text),
    #"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"URL - Copy", "URLref"}})
in
    #"Renamed Columns1"

This is returned: 

http://phx.corporate-ir.net/External.File?item=UGFyZW50SUQ9Njk2MDEyfENoaWxkSUQ9NDA3NDE0fFR5cGU9MQ==&...

In my 2nd query, I'd like to reference this URL from my href_query.

let
    Source = Excel.Workbook(Web.Contents(href_query[URLref]), null, true),
    #"Removed Bottom Rows" = Table.RemoveLastN(Source,2),
    #"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",3),
    #"Removed Bottom Rows1" = Table.RemoveLastN(#"Removed Top Rows",1),
    #"Master Data" = #"Removed Bottom Rows1"{[Name="Master Data"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Master Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"County", type text}, {"Basin", type text}, {"DrillFor", type text}, {"Location", type text}, {"Trajectory", type text}, {"WellDepth", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Week", Int64.Type}, {"RigCount", Int64.Type}, {"State/Province", type text}, {"PublishDate", type datetime}})
in
    #"Changed Type"

I receive the following error:

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=List
    Type=Type

Admittedly, my code may be incorrect, but any help to reference the contents of my href_query into Query 2, would be very helpful.  Alternative, if anybody knows of a direct solution to scrape the href URL directly with power BI, then I could eliminate the intermediary step using google sheets.

Thanks in advance!

[ImkeF · Accepted solution]

Is the error returned in the first step already?

Then please try this:

let
    Source = Excel.Workbook(Web.Contents(href_query[URLref]{0}), null, true),
    #"Removed Bottom Rows" = Table.RemoveLastN(Source,2),
    #"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",3),
    #"Removed Bottom Rows1" = Table.RemoveLastN(#"Removed Top Rows",1),
    #"Master Data" = #"Removed Bottom Rows1"{[Name="Master Data"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Master Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"County", type text}, {"Basin", type text}, {"DrillFor", type text}, {"Location", type text}, {"Trajectory", type text}, {"WellDepth", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Week", Int64.Type}, {"RigCount", Int64.Type}, {"State/Province", type text}, {"PublishDate", type datetime}})
in
    #"Changed Type"