Re: API Call - Merge Query

CEllis · ‎03-21-2024

I am not sure how to get this to work but looking on the forums here it look like you cant do an API call on 2 different tables then merge one with the other.

The error comes back when the data is refreshed in PowerBi Services dataflow.

Error: [Unable to combine data] Section1/table/Replace errors references other queries or steps so it may not directly access a data source. Please rebuild this data combination.

This does work when editing the data in PowerBi Servcies dataflow.

What I think I need to be able to do this is bring all the code together in one query to stop table 2 calling the API outside of the query when the merge happens.

My question is - how do I combine table 1 and table 2 into 1 query to stop the API been called outside of the main query 'table2'

This is the

APICall

(PageNum as number, _A as text,_B as text,_C as text)=>

let

     Username = "xxxx",
     Password = "xxxx",
     UserPass = Username & ":" & Password,
     Bytes = Text.ToBinary(UserPass),
     Base64_UserPass = Binary.ToText(Bytes, BinaryEncoding.Base64),

    apiUrl = "https://xxx.xxxxxxxxxx.xxxx/", 
    relpath = "xxxxx/xxxxxxxx" & _A &  _B ,			

    header = [#"Authorization" = "xxxxxxx " & Base64_UserPass, #"Content-Type" = "application/json"],	

    result = Json.Document(


        Web.Contents(
            apiUrl, 
            [
                RelativePath=relpath, 
                Headers = header,                 
                Query = [page=Number.ToText(PageNum), include=_C]
            ]
            )
        ),


    data = result[data]
in
    data

This calls the APICall

PageCall

(PageNum as number,_A as text ,_B as text, _C as text)=>
let 

Source = List.Generate(()=>
        [Result = try APICall(PageNum,_A,_B,_C) otherwise {}, Page=1],
        each [Result]<>{},
        each [Result = try APICall([Page]+PageNum,_A,_B,_C) otherwise {}, Page=[Page]+PageNum],
        each [Result])

in
  Source

This is table 1 that will be merged with table 2 - this calls the API directly becaseu there is only 70 records and no paging required.

table1

let
  Source = APICall(1, "/table1", "", "xxxxxxxx"),
  #"Convert to table" = Table.FromValue(Source),
  #"Expanded Value 2" = Table.ExpandRecordColumn(#"Convert to table", "Value", {"id", "code", "description", "type"}, {"id", "code", "description", "type"}),
  #"Transform columns" = Table.TransformColumnTypes(#"Expanded Value 2", {{"id", type text}, {"code", type text}, {"description", type text}, {"type", type text}})
  
in
 #"Transform columns"

This is table 2 that will use table 1 to mege using the code - this calls the PageCall because there are 1000's of records.

table2

let
  Source = PageCall(1, "/table2", "", "xxxxxxxx"),
  #"Converted to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expanded Column1" = Table.ExpandListColumn(#"Converted to table", "Column1"),
  #"Expanded Column1 1" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "date", "comment", "session"}, {"id", "date", "comment", "session"}),
  #"Merged queries" = Table.NestedJoin(#"Changed column type 1", {"id"}, table1, {"id"}, "table1", JoinKind.LeftOuter)
in
   #"Merged queries"

Hope this makes some sence