cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johan
Helper III
Helper III

Use parameter for multiple query steps

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

2 ACCEPTED SOLUTIONS
artemus
Microsoft
Microsoft

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

View solution in original post

Jimmy801
Super User III
Super User III

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

View solution in original post

2 REPLIES 2
Jimmy801
Super User III
Super User III

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

View solution in original post

artemus
Microsoft
Microsoft

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors