Power Query URL Change

[msarfaraz]

let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & "BTC-USD" & "?period1=1581415348&period2=1613037748&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"



How I can change the above query instead of "BTC-USD" I want a column (Text field) to pick the dynamically.

 

[Norie]

Change "BTC-USD" to refer to the relevant column.

 

[JustynaMK]

First, you need to transform your query into a function by adding one more line at the top, and then referencing your new variable in the web link:

(ticker as text) as table =>
let
Source = Csv.Document(Web.Contents("https://query1.finance.yahoo.com/v7/finance/download/" & ticker & "?period1=1581415348&period2=1613037748&interval=1d&events=history&includeAdjustedClose=true"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Adj Close", type number}, {"Volume", Int64.Type}})
in
#"Changed Type"

Second, you create a separate query with just one column, which will be your list of tickers (not sure if these tickers even exist, just taking a guess ).

[ATTACH type="full"]31816[/ATTACH]

The last step is to go to Add Column > Invoke Custom Function > choose the first query as your Function query > parse Tickers column as ticker parameter.

Afterwards, click on "double arrow" icon to expand your formula results.

 

[msarfaraz]

List of ticker is available in table (which have only one column)

 

[JustynaMK]

Perfect! So now you can apply step no 3 to your CoinsSymbol query, which is:


The last step is to go to Add Column > Invoke Custom Function > choose the first query as your Function query > parse Tickers column as ticker parameter.

Afterwards, click on "double arrow" icon to expand your formula results.

 

[msarfaraz]

Perfect! So now you can apply step no 3 to your CoinsSymbol query, which is:


The last step is to go to Add Column > Invoke Custom Function > choose the first query as your Function query > parse Tickers column as ticker parameter.

Afterwards, click on "double arrow" icon to expand your formula results.

Get this error

 

[JustynaMK]

It looks like your tickers column (CoinsSymbol query) was build using some other sources. Can you please try first to create a "static" Power Query (by using "Enter Data"), enter some 3-5 tickers that you know will work, and invoke the function again? We'll take it from there.

 

[msarfaraz]

From there it works, can it possible to take from that table since it's dynamic and will update once a new coin will be available?

 

[JustynaMK]

I didn't experience such issue myself but it looks like the solution might be to go to File > Options and Settings > Options > Privacy > Ignore Privacy Levels...

Formula.Firewall: Query references other queries, so it may not directly access a data source.

I am getting the following error: Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. In my first query I am accessing an API to get a list of URLs. In the second query...

community.powerbi.com

Power Query Errors: Please Rebuild This Data Combination

This post looks at how to structure your Power Query setup to avoid the "please rebuild this data combination error" when you try to merge two queries.

www.excelguru.ca

 

[msarfaraz]

I didn't experience such issue myself but it looks like the solution might be to go to File > Options and Settings > Options > Privacy > Ignore Privacy Levels...

Formula.Firewall: Query references other queries, so it may not directly access a data source.

I am getting the following error: Formula.Firewall: Query 'Query1' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. In my first query I am accessing an API to get a list of URLs. In the second query...

community.powerbi.com

Power Query Errors: Please Rebuild This Data Combination

This post looks at how to structure your Power Query setup to avoid the "please rebuild this data combination error" when you try to merge two queries.

www.excelguru.ca