Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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
User | Count |
---|---|
45 | |
26 | |
21 | |
18 | |
18 |
User | Count |
---|---|
53 | |
45 | |
25 | |
24 | |
21 |