Forum Discussion

UTexas80's avatar
UTexas80
Regular Visitor
9 years ago
Solved

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...
  • Greg_Deckler's avatar
    9 years ago

    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.