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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlexHeinze
Frequent Visitor

Switch datasource using IF statement in Power Query

I have a report with 2 datasources.

One datasource gets the XML from a file (Source = Xml.Tables(File.Contents(.......):

AlexHeinze_0-1718376224749.png

The second datasource gets a similar XML from an API (Source = Xml.Tables(Web.Contents(.......):

AlexHeinze_1-1718376260057.png

My report has a parameter "Datasource", which is then utilized in Power Query to select file or API datasource:

let
SourceSelection = DataSource,
// Select the sources based on the parameter value
Source = if SourceSelection="API Broadcast" then #"_Get XML from API Broadcast" else #"_Get XML from File"
in
Source

 

When the parameter is set to file, the returned datasource is correct (column type=table):

AlexHeinze_2-1718376469046.png

 

However when I set it to API, the returned datasource is wrong (column type=alpha):

AlexHeinze_3-1718376524539.png

 

Any idea what's wrong here?

 

1 ACCEPTED SOLUTION

I finally solved it by not referencing the existing sources, instead I put it all in the same place:

let
    // Get data from API
    SourceAPI = Xml.Tables(Web.Contents(GatewayURL)),
    
    // Get data from file
    SourceFile = Xml.Tables(File.Contents(SavedScheduleXML)),
    
    // Select the sources based on the parameter value
    Source = if DataSource="API Broadcast" then SourceAPI else SourceFile,
.......

View solution in original post

6 REPLIES 6
WanderingBI
Resolver III
Resolver III

I would first check if the problem really comes from the IF statement by simplifying to this first and checking the output:

 

let
Source =  #"_Get XML from File"
in
Source

 

 

This step seems redundant (assigning the parameter to a new variable):

SourceSelection = DataSource,

Thanks! I had tried this already, referencing the datasource worked for both the API and XML. Here's an example:

let
Source1 = #"_Get XML from API Broadcast",
Source2 = #"_Get XML from File",
// This one works
// Source = Source1
// This one does not work
Source = if (1=1) then Source1 else Source2

in
Source

 

With the inverted condition and swapped Source 1/2 it doesn't work either. Very strange.

That is indeed very strange and does not conform with my understanding of the evaluation model.

 

Maybe isolating Source1 and Source2 declaration from the if statement by putting the if into another let/in environment could help:

 

let
Source1 = #"_Get XML from API Broadcast",
Source2 = #"_Get XML from File",

getData = (choice as text) => 
let
 source = if choice = "api" then Source1 else if choice = "file" then Source2 else null
in
 source

Source = getData("api")

 

 

 

Thanks for looking into this, @WanderingBI . I had to tweak the code slightly...

let
Source1 = #"_Get XML from API Broadcast",
Source2 = #"_Get XML from File",

getData = (choice as text) => 
let
 funcsource = if choice = "api" then Source1 else if choice = "file" then Source2 else null
in 
 funcsource,

Source = getData ("api")

in

Source

...unfortunately the result for "api" is still broken (while it still works for "file":

AlexHeinze_0-1718789109665.png

 

Wish I could do more than guessing.

 

When assuming that the issue is only that the column "Table" is of data type any instead of table and this messes with the software receiving the query output (Power BI), would maybe setting the column type to table manually do any good?

 

let
Source1 = #"_Get XML from API Broadcast",
Source2 = #"_Get XML from File",

getData = (choice as text) => 
let
 funcsource = if choice = "api" then Source1 else if choice = "file" then Source2 else null
in 
 funcsource,

Source = getData ("api"),
SourceAdjustColumnType = Value.ReplaceType(Source, type table [Table = Table.Type])

in

SourceAdjustColumnType


 

 

I finally solved it by not referencing the existing sources, instead I put it all in the same place:

let
    // Get data from API
    SourceAPI = Xml.Tables(Web.Contents(GatewayURL)),
    
    // Get data from file
    SourceFile = Xml.Tables(File.Contents(SavedScheduleXML)),
    
    // Select the sources based on the parameter value
    Source = if DataSource="API Broadcast" then SourceAPI else SourceFile,
.......

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors