Multiple api calls in Power BI

Hi geekyPanda 

When you say you've got an "empty table", you mean it HAS rows, but all cells are null (like below)?

I think the problem is with "extand" - the toTable should have "Column1" that has inside a record with 2 x fields : "i" and "data" - I think you would first need to expand "data" and then extract from data the columns you want.

You can run below query in normal PowerQuery and see what's inside toTable and decide what you need to expand

let
    getMydata.getMyData = (apiKey as text, symbols as text, startYear as text, endYear as text) =>
        let
            symbolList = Text.Split(symbols, ","),
            initialPosition = 0,
            getJson = (position) =>
                let
                    source = Json.Document(
                        Web.Contents(
                            "https://myDataAPI.com/getData.json?apikey="
                                & apiKey
                                & "&symbol="
                                & symbolList{position}
                                & "&startYear="
                                & startYear
                                & "&endYear="
                                & endYear
                        )
                    ),
                    apiData = try source[results] otherwise null
                in
                    apiData,
            AllJson = List.Generate(
                // initial
                () => [i = 1, data = getJson(initialPosition)],
                // condition
                each [i] <= List.Count(symbolList),
                // next
                each [i = [i] + 1, data = getJson([i])]
            )
            ,
            toTable = Table.FromList(AllJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
        in
            toTable,
    tst = getMydata.getMyData("x", "GOOG,AAPL", "y", "z")
in
    tst

Please mark this as answer if it helped.

Hi geekyPanda 

Rule of thumb IMO - you have to first see exactly what you want in PowerBI GUI (this way you generate/test the M code), then embed that code in the connector.

Question: Did you reach to the point where you saw in PowerBI GUI exactly the table that you wanted to be returned by the connector (after expanding...)?

If YES, then you need to incorporate in your getMydata.getMyData function the ALL the M code that lies OUTSIDE of it (all those expansions) - you can see the code in PowerBI GUI "Advanced Editor".

---

From the pics it shows you still need to expand 2 x times.

Ex. IF for example inside the record you have "cola", "colb" and "colc" fields that you want to see, then inside your above mentioned getMydata.getMyData you need to REPLACE

        in
            Expand;

with

            ,
            #"Expanded history" = Table.ExpandListColumn(Expand , "history"),
            #"Expanded history1" = Table.ExpandRecordColumn(#"Expanded history", "history", {"cola", "colb", "colc"}, {"cola", "colb", "colc"})
        in
            #"Expanded history1"

---

You also probably need to remove "i" column... -> In PowerBI GUI right click on the column and select remove, then look at the automatically generated code in Advanced Editor, then incorporate that code in your function