Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
CEllis
Resolver I
Resolver I

API Call - Merge Query

 

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

 

 

 

3 REPLIES 3
lbendlin
Super User
Super User

Don't merge them.  Let the data model do that work for you.

Hi @lbendlin 

 

I have found that if I do a new merged table it works but if I try to merge table 1 into table 2 it failes on the refrsh schedule.

 

 

Don't merge them.  Let the data model do that work for you

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.