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

 

WanderingBI's avatar
WanderingBI
Icon for Resolver III rankResolver III
2 years ago

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

 

 

  • AlexHeinze's avatar
    AlexHeinze
    Frequent Visitor
    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,
.......