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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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