Forum Discussion
Multiple api calls in Power BI
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;
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 tstPlease 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
9 Replies
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 tstPlease mark this as answer if it helped.
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?
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.
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]] ) ),
