Forum Discussion
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 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
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.
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.
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?