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

[frano72]

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...

[frano72]

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

[BA_Pete]

Thanks.

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

Pete

[BA_Pete]

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

[frano72]

@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 =="

[BA_Pete · 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