Forum Discussion
frano72
Helper IV
Helper IVif 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...
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 xformationsPete
frano72Helper IV
Helper IVBA_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
Super User
Super UserHi 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