Conditional Source Statement

[ThomasDay]

Hello all,

Also @MarcelBeug , @Kimcha 

I've seen a 2 year old thread that touches on this issue and haven't been able to connect, so am putting it into play again.  Here goes and any help is appreciated

I would like to create "conditional" Source statements....

• I have a situation where I have two machines that can be used to refresh our many large models. 
• The queries however need to be told which machine is being used for the refresh-->this in turn determines the file location and the SQL servers to be used.
• So, I have isolated the sources in each model and I would like to give the queries a parameter which tells it which source stmt to use.  I regretably do not know M except what I've read in M is for data Monkey.  It might look something like this:

let
 SourceMachine1 = Csv.Document(File.Contents("C:\Users\Mach1\Models\DataLoad\BigFile.csv"),[Delimiter=",", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers1" = Table.PromoteHeaders(SourceMachine1),
 
 SourceMachine2 = Csv.Document(File.Contents("C:\Users\Mach2\Models\DataLoad\BigFile.csv"),[Delimiter=",", Columns=24, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers2" = Table.PromoteHeaders(SourceMachine2),
//pick the machine with a parameter, use_No1 (either true of false)
 #WhichToUse = if use_No1 then #"Promoted Headers1" else #"Promoted Headers2"
 
 //------>then the rest of the query which is all the same for both machines
#"Renamed Columns" = Table.RenameColumns(#"WhichToUse",{{"UniformName", "ClientName"}, {"TFCB", "Beds"}, {"UHN", "FACIL(ReptID)"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([RankReptRecFYs] = 1) and ([ReptVersion] = "Current"))
in
    #"Filtered Rows"

Is that remotely correct?  So how do I feed in the parameter that I've called use_No1?  Do I have set it in the query itself or can it tell what machine it's on?

Anyway, any help on the above or a better strategy would be very much appreciated.

Thank you in advance,

Tom

[v-chuncz-msft · Accepted solution]

@ThomasDay 

You may invoke custom function in If expression.

https://community.powerbi.com/t5/Desktop/Create-tables-based-on-all-Query-Parameters/td-p/345947

[v-chuncz-msft · Accepted solution]

@ThomasDay 

You may invoke custom function in If expression.

https://community.powerbi.com/t5/Desktop/Create-tables-based-on-all-Query-Parameters/td-p/345947

[ThomasDay · Accepted solution]

Hello all,

It turns out that Power BI has a very useful ability to change data sources in a flash.  It was extremely helpful and I've chosen not to mess with my queries as a result.

If you go to File->Options and settings->Data source settings you'll be presented with a summary of all of your data sources in that model and an ability to easily and quickly change the sources of all of the queries connected to each one.  The change sources button presents a dialog box with basic and advanced options.  It was really simple and fast to do what I needed to do.  Change the file path and hit OK.  The advanced tab has more options for adding to a source string.

Cheers!  Tom