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.Tab...
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,
.......
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")

       

       

       

      • AlexHeinze's avatar
        AlexHeinze
        Frequent Visitor
        2 years ago

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