Forum Discussion
Expression Error - #""Changed Type""
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=TypeI 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
SourceI am not sure where to start the troubeshooting process, so any assistance in pointing me in the right direction is greatly appreciated.
Thank you.
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.
4 Replies
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.
Thank you for the time you took to explain the solution. I appreciate it very much.
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®ion=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®ion=usa&culture=en-US
http://financials.morningstar.com/ratios/r.html?t=MSFT®ion=usa&culture=en-US
http://financials.morningstar.com/ratios/r.html?t=IBM®ion=usa&culture=en-US
...etc
THANKS for all of your value-added posts.
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...
