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 uncomment to switch.

 

Must be better way...

 

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

5 Replies

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

       

      Thanks.

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

       

      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