Forum Discussion

frano72's avatar
frano72
Icon for Helper IV rankHelper IV
4 years ago
Solved

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 uncomme...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

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's avatar
    frano72
    Icon for Helper IV rankHelper IV
    4 years ago

    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's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

      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