Forum Discussion

AlexHeinze's avatar
AlexHeinze
Frequent Visitor
2 years ago
Solved

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(.......):

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

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):

 

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

 

Any idea what's wrong here?

 

Need Help
  • AlexHeinze's avatar
    AlexHeinze
    2 years ago

    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,
.......

6 Replies

  • WanderingBI's avatar
    WanderingBI
    Resolver III
    2 years ago

    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,

    • AlexHeinze's avatar
      AlexHeinze
      Frequent Visitor
      2 years ago

      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.

      • WanderingBI's avatar
        WanderingBI
        Resolver III
        2 years ago

        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")