Use parameter for multiple query steps

[Johan]

Hi,

I've used the parameters a lot, to change the source from a dev to prod database for example.

Now I need a bit more complex situation

The dev database is in dataflows, the prod is in datalake.

This makes the steps to read from the source different, it's not a single source reference to change, multiple steps need to change.

Like:

if pSource = 'dev' then 

 Source = #"SourceFiles",
 #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "SourceFile.csv"),
 Content = #"Filtered Rows"{0}[Content],
  #"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=91, Encoding=1252, QuoteStyle=QuoteStyle.None]),
  #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),

else

   Source = PowerBI.Dataflows(null),
   #"WorkspaceID" = Source{[workspaceId="1234abcd"]}[Data],
   #"DataflowID" = #"WorkspaceID"{[dataflowId="abcd1234"]}[Data],
   #"FileToRead" = #"DataflowID"{[entity="SourceFile"]}[Data]

anyone has suggestions how to implement this?

Thanks,

Johan

[artemus · Accepted solution]

Make 2 queries, both set to not load to model. Then add one more query that does the ife

if pSource = "dev" then
   DevQuery
eles
   ProdQuery

Note: the security model will require you to sign into both

[Jimmy801 · Accepted solution]

Hello @Johan 

write the query like this

ReadDataSource = if pSource = 'dev' then 
let
 Source = #"SourceFiles",
 #"Filtered Rows" = Table.SelectRows(Source, each [Name] = "SourceFile.csv"),
 Content = #"Filtered Rows"{0}[Content],
  #"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=91, Encoding=1252, QuoteStyle=QuoteStyle.None]),
  #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true])
in 
 #"Promoted Headers"

else
let
   Source = PowerBI.Dataflows(null),
   #"WorkspaceID" = Source{[workspaceId="1234abcd"]}[Data],
   #"DataflowID" = #"WorkspaceID"{[dataflowId="abcd1234"]}[Data],
   #"FileToRead" = #"DataflowID"{[entity="SourceFile"]}[Data]
in 
   #"FileToRead"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[artemus · Accepted solution]

Make 2 queries, both set to not load to model. Then add one more query that does the ife

if pSource = "dev" then
   DevQuery
eles
   ProdQuery

Note: the security model will require you to sign into both