cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frano72
Helper IV
Helper IV

if statement to swap workspaceid's/dataflowid's ?

Hi,

 

Does anyone know how i can refactor this to be a bit cleaner....it's a pq query from pbi desktop that switches the calendar query between US and AU calendars.  I currently comment and uncomment to switch.

 

Must be better way...

 

2021-10-14_17-32-28.jpg

1 ACCEPTED SOLUTION

Hi @frano72 ,

 

Try this, noting the following changes:

1) I've changed your toggle query to be called "calendarToggle" to make it more intuitive viewing in the query list.

2) I've changed the values that your calendarToggle query holds to "au" and "us" to, again, make it more intuitive/obvious when changing values.

 

let
    //Build AU source
	SourceAU = PowerPlatform.Dataflows(null),
    WorkspacesAU = SourceAU{[Id="Workspaces"]}[Data],
    WorkspaceAU = WorkspacesAU{[workspaceId="6237640d-aed3-4d7a-9f62-4aeafcf26be8"]}[Data],
    CalendarDFAU = WorkspaceAU{[dataflowId="26666466-5b93-4620-a1eb-94e8a144818c"]}[Data],
	CalendarAU = CalendarDFAU{[entity="Calendar",version=""]}[Data],
    //Build US source
	SourceUS = PowerPlatform.Dataflows(null),
    WorkspacesUS = SourceUS{[Id="Workspaces"]}[Data],	
    WorkspaceUS = WorkspacesUS{[workspaceId="47a9fb59-a273-487f-9142-fdba16f78ef4"]}[Data],
    CalendarDFUS = WorkspaceUS{[dataflowId="bb174af9-7cf5-4426-899f-85ea9102eee6"]}[Data],
    CalendarUS = CalendarDFUS{[entity="Calendar",version=""]}[Data],
	//Use toggle query to select source to use
	selectCalendar =
		if calendarToggle = "au"
		then CalendarAU
		else CalendarUS
		
	/* ***********
	Remaining xformation steps go here
	*********** */
in
    selectCalendar // change to final step name after other xformations

 

 

Pete

View solution in original post

5 REPLIES 5
frano72
Helper IV
Helper IV

@BA_Pete - stop work.  grab a beer. turn on netflix.  it worked !

 

Thanks.

I'll just call my boss and let her know that you've authorised this, then get right on it! 😂

 

Pete

BA_Pete
Super User
Super User

Hi @frano72 ,

 

I don't think there's any way to do it fully-dynamically with M code, but you could make it easier by just using a toggle query, if that's what you're after.

 

You would create a new query in PQ called "workspaceToggle" or similar, ad just have it contain a single value, 1 or 0.

Then rename your #"Calendar DF" steps to 'calendarAU' and 'calendarUS'.

Underneath your new calendar US step, you could then add a line something like this:

 

calendarBase =
if workspaceToggle = 0 then calendarAU
else if workspaceToggle = 1 then calendarUS
else...

 

 

This should set your calendar base step to be the correct calendar step and you can then put subsequent steps after this.

 

NOTE: you will probably need to set up each individual calendar source into a full source block and include your 'Calendar' step in each.

If you can post your M code that you screen-grabbed into a code window here I'll rejig it for you.

 

Pete

@BA_Pete - here you go !  Thanks so much .... !

let
    Source = PowerPlatform.Dataflows(null),
    Workspaces = Source{[Id="Workspaces"]}[Data],
    // need to modify the two lines that reference workspaceid and dataflowid to switch between US and AU calendars
    #"AU Workspace" = Workspaces{[workspaceId="6237640d-aed3-4d7a-9f62-4aeafcf26be8"]}[Data],
    #"Calendar DF" = #"AU Workspace"{[dataflowId="26666466-5b93-4620-a1eb-94e8a144818c"]}[Data],
    //#"US Workspace" = Workspaces{[workspaceId="47a9fb59-a273-487f-9142-fdba16f78ef4"]}[Data],
    //#"Calendar DF" = #"US Workspace"{[dataflowId="bb174af9-7cf5-4426-899f-85ea9102eee6"]}[Data],
    // nothing to modify after this line
    Calendar = #"Calendar DF"{[entity="Calendar",version=""]}[Data],
    #"==Modify Source for AU or US Calendar ==" = Calendar
in
    #"==Modify Source for AU or US Calendar =="

Hi @frano72 ,

 

Try this, noting the following changes:

1) I've changed your toggle query to be called "calendarToggle" to make it more intuitive viewing in the query list.

2) I've changed the values that your calendarToggle query holds to "au" and "us" to, again, make it more intuitive/obvious when changing values.

 

let
    //Build AU source
	SourceAU = PowerPlatform.Dataflows(null),
    WorkspacesAU = SourceAU{[Id="Workspaces"]}[Data],
    WorkspaceAU = WorkspacesAU{[workspaceId="6237640d-aed3-4d7a-9f62-4aeafcf26be8"]}[Data],
    CalendarDFAU = WorkspaceAU{[dataflowId="26666466-5b93-4620-a1eb-94e8a144818c"]}[Data],
	CalendarAU = CalendarDFAU{[entity="Calendar",version=""]}[Data],
    //Build US source
	SourceUS = PowerPlatform.Dataflows(null),
    WorkspacesUS = SourceUS{[Id="Workspaces"]}[Data],	
    WorkspaceUS = WorkspacesUS{[workspaceId="47a9fb59-a273-487f-9142-fdba16f78ef4"]}[Data],
    CalendarDFUS = WorkspaceUS{[dataflowId="bb174af9-7cf5-4426-899f-85ea9102eee6"]}[Data],
    CalendarUS = CalendarDFUS{[entity="Calendar",version=""]}[Data],
	//Use toggle query to select source to use
	selectCalendar =
		if calendarToggle = "au"
		then CalendarAU
		else CalendarUS
		
	/* ***********
	Remaining xformation steps go here
	*********** */
in
    selectCalendar // change to final step name after other xformations

 

 

Pete

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors