Multiple api calls in Power BI

[geekyPanda]

I'm building custom data connector for Power BI and I'm pretty new in this, so I have to build data connector with multiple api calls based on list of symbols which are entered in api call.

For example:

I wrote this logic below, and I didn't get any error when I ran it, but I got empty table, I'm not sure what is wrong here, any help would be appreciated.

This api call "https://myDataAPI.com/getData.json?apikey=myApiKey&symbol=GOOG,AAPL&startYear=2021&endYear=2023" should return data for GOOG and AAPL symbol in the same table.

shared getMydata.getMyData = (apiKey as text, symbols as text, startYear as  text, endYear as text) =>
	let 
		symbolList = Text.Split(symbols , ","),
		position = 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(
			() => [i = 1, data = getJson(position)],
			each [i] <= List.Count(symbolList),
			each [i = [i]+1, data = getJson([i])]
		),

		toTable = Table.FromList(AllJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
		extand = Table.ExpandRecordColumn(toTable, "Column1", {"symbol", "startYear", "endYear" })
	in 
		extand;

[ams1 · Accepted solution]

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.

[Poohkrd]

Hi! Read this article from guru and this video.

I recommend use this style, when use Web.Contents with multiple calls:

source = Json.Document(
  Web.Contents(
    "https://myDataAPI.com/getData.json",
    [Query = [apikey=apiKey, symbol=symbolList{position}, startYear=startYear , endYear=endYear]]
  )
),

[ams1 · Accepted solution]

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.

[geekyPanda]

This was very helpful, when I run code now I get output two records, can please give me some hints how shoud I extand data here I'm a little confused? 

[ams1]

Hi @geekyPanda 

Yeah, your output is 2 x rows (1 row for each symbol), with 1 column, each having inside a record, like below:

If you click that expand button in the top-right of the column...

...and then choose to expand only the data field (like above), you will see what's inside your api response. Then you'll probably have to again expand that...

If you've got what you need, use that PowerQuery code in your custom connector.

Please mark this as answer if it helped.

[geekyPanda]

I did following steps to extract data, but  I didn't get table. I'm missing something, but I don't know what exactly. Here is my code and results in Power BI. 
` ` `

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,
                    data = if apiData <> null then apiData[data] else null, // extract "data"
                    toTable = if data <> null then Table.FromRecords(data) else null 
                in
                    toTable,
            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) // combine all tables into one
            ExpandList = List.Distinct(List.transform(Table.Column(toTable, "Column1"), each if _ is record then Record.FieldNames(_) else{}))),

Expand = Table.ExpandRecordColumn(toTable, "Column1", ExpandList, ExpandList)

        in
            Expand;

 When I run this data connector in Power BI I got this this is when I open data in Power BIThis is what I got when I clicked on one of these lists

When I clicked on these records I got table with attributes which I need. Name of the columns are a little different because I use actual API. My question is how can I convert these lists into table? 

[ams1]

Hi @geekyPanda 

Like I've said, keep pushing the expand button (below) until... you don't see it anymore 😊

Restart from the query I've sent and please push that expand button at least 3 x times (shoud be: 1 x to expand "data", 1 x to expand lists, 1 x to expand the columns you need from the records)!

[geekyPanda]

My question is not how to do it via the power bi GUI, but can I write the code in data connector that will open table immediately with all attributes when I open power BI without having to click things.

[ams1 · Accepted solution]

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

[geekyPanda]

Thank you so much for all help. It works!