cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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=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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Expression Error - #""Changed Type""

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Highlighted
Memorable Member
Memorable Member

Re: Expression Error - #""Changed Type""

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...

 

www.CahabaData.com
Highlighted
Super User IV
Super User IV

Re: Expression Error - #""Changed Type""

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Regular Visitor

Re: Expression Error - #""Changed Type""

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

Highlighted
New Member

Re: Expression Error - #""Changed Type""

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors