Expression Error - #""Changed Type""

[UTexas80]

Hello - This is my first attempt at creating a solution in Power BI. I am following a tutorial "How to Download Stock Price Data into Power BI". I am getting the following error:

Expression.Error: We cannot convert the value "(GetData) =>
 
let
 ..." to type Function.
Details:
    Value=(GetData) =>
 
let
 
Source = Csv.Document(Web.Contents("https://www.quandl.com/api/v3/datatables/WIKI/PRICES.csv?date.gte=20050101&date.lte=" & Number.ToText(Date.Year(DateTime.FixedLocalNow())) & Number.ToText(Date.Month(DateTime.FixedLocalNow())) & Number.ToText(Date.Day(DateTime.FixedLocalNow())) &"&ticker=" & Text.From(GetData) & " &api_key=ZtvE2BmA_dxMQvnn_mWU"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ticker", type text},{"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"ex-dividend", type number},{"split_ratio", type number},{"adj_open", type number},{"adj_high", type number},{"adj_low", type number},{"adj_close", type number},{"adj_volume", Int64.Type }})
in
#"Changed Type"
    Type=Type

I think this error is referencing back to the "Changed Type" code in the "StockQuotes" query

let
    Source = "(GetData) =>#(lf) #(lf)let#(lf) #(lf)Source = Csv.Document(Web.Contents(""https://www.quandl.com/api/v3/datatables/WIKI/PRICES.csv?date.gte=20050101&date.lte="" & Number.ToText(Date.Year(DateTime.FixedLocalNow())) & Number.ToText(Date.Month(DateTime.FixedLocalNow())) & Number.ToText(Date.Day(DateTime.FixedLocalNow())) &""&ticker="" & Text.From(GetData) & "" &api_key=ZtvE2BmA_dxMQvnn_mWU""),[Delimiter="","", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),#(lf) #(lf)#""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),#(lf)#""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""ticker"", type text},{""date"", type date}, {""open"", type number}, {""high"", type number}, {""low"", type number}, {""close"", type number}, {""volume"", Int64.Type}, {""ex-dividend"", type number},{""split_ratio"", type number},{""adj_open"", type number},{""adj_high"", type number},{""adj_low"", type number},{""adj_close"", type number},{""adj_volume"", Int64.Type }})#(lf)in#(lf)#""Changed Type"""
in
    Source

I am not sure where to start the troubeshooting process, so any assistance in pointing me in the right direction is greatly appreciated.

Thank you.

[Greg_Deckler · Accepted solution]

I got this working, the directions are less than clear.

Create all your queries using a new blank query and then open Advanced Editor and paste in the code.

Make sure your custom function is called "StockQuotes"

(GetData) =>
 
let
 
Source = Csv.Document(Web.Contents("https://www.quandl.com/api/v3/datatables/WIKI/PRICES.csv?date.gte=20100101&date.lte=" & Number.ToText(Date.Year(DateTime.FixedLocalNow())) & Number.ToText(Date.Month(DateTime.FixedLocalNow())) & Number.ToText(Date.Day(DateTime.FixedLocalNow())) &"&ticker=" & Text.From(GetData) & "&api_key=Zx8Z9a6MLmPGvoav_-U1
"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
 
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ticker", type text},{"date", type date}, {"open", type number}, {"high", type number}, {"low", type number}, {"close", type number}, {"volume", Int64.Type}, {"ex-dividend", type number},{"split_ratio", type number},{"adj_open", type number},{"adj_high", type number},{"adj_low", type number},{"adj_close", type number},{"adj_volume", Int64.Type }})
in
#"Changed Type"

The next one needs to be called "Stock Tickers" (and one of their columns is named wrong, this corrects it):

let
Source = Table.FromRecords({
[Ticker = "GE", Company = "General Electric Company"],
[Ticker = "IBM", Company = "International Business Machines"],
[Ticker = "MSFT", Company = "Microsoft Corp."]
}),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Ticker", "Tickers"}})
in
#"Renamed Columns"

And this one should be called "Data Table"

let
Source = #"Stock Tickers",
Tickers = Source[Tickers],
 
#"Converted to Table" = Table.FromList(Tickers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each StockQuotes([Column1])),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"date", "open", "high", "low", "close", "volume", "adj_close"}, {"Custom.date", "Custom.open", "Custom.high", "Custom.low", "Custom.close", "Custom.volume", "Custom.adj_close"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom1",{{"Column1", "Ticker"}, {"Custom.date", "Date"}, {"Custom.open", "Open"}, {"Custom.high", "High"}, {"Custom.low", "Low"}, {"Custom.close", "Close"}, {"Custom.volume", "Volume"}, {"Custom.adj_close", "Adj. Close"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Open", type number}, {"High", type number}, {"Low", type number}, {"Close", type number}, {"Volume", Int64.Type}, {"Adj. Close", type number}})
in
#"Changed Type"

 Make sure to authenticate to quandl as Anonymous.

[BAnon]

smoupre - This was really helpful. Have you ever tried to do something similar with "key ratios"  from morningstar.com? http://financials.morningstar.com/ratios/r.html?t=GE&region=usa&culture=en-US

I've been trying to re-create what you did, but I'm a novice and just beginning to learn code. I'm advanced at doing easy things like scraping individual stock data from various sites. I get into trouble when I try to get information for multiple stocks.

It's obviously not efficient to keep copying and pasting individual stock tickers into BI.

http://financials.morningstar.com/ratios/r.html?t=GE&region=usa&culture=en-US

http://financials.morningstar.com/ratios/r.html?t=MSFT&region=usa&culture=en-US

http://financials.morningstar.com/ratios/r.html?t=IBM&region=usa&culture=en-US

...etc

THANKS for all of your value-added posts.

[UTexas80]

Thank you for the time you took to explain the solution. I appreciate it very much.

[CahabaData]

have you changed csv files?   meaning that you built some modeling on another csv file....    if so, go back to your original csv and trigger the Get Data and sanity check that...