Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
32 | |
27 | |
27 | |
26 |
User | Count |
---|---|
61 | |
56 | |
34 | |
29 | |
28 |