You’re offline. This is a read only version of the page.
111
Hi Team,
I am wondering if you are able to please help me correct my query. I am wanting to take multiple values from my "DistroList" field in SharePoint which is "Value1; Value2" (This is how it's passed to the SharePoint by my PowerApp).
This is my query (I am able to get 1 result, but as soon as it has more than one) then it's no good.
Update - I was able to solve this like below.
Valuable if someone needs to take more than one value from a Sharepoint list and run a query against a dataset in Power Automate
Share
Report
All responses (
Answers (
DEFINE
// This variable holds the output from the DL field in Power Automate.
VAR DLFieldValue = "@{triggerBody()?['DL']}"
// Output from the DL field
// This replaces the semicolon delimiter in the string with a pipe character for easier processing.
VAR PipeDelimitedValues =
SUBSTITUTE ( DLFieldValue, "; ", "|" )
// Replace delimiter with a pipe
// This creates a table of business areas by generating a series of numbers based on the number of items.
VAR BusinessAreaList =
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( PipeDelimitedValues ) ),
// Generate numbers from 1 to the number of items
"BusinessArea",
// Name the new column "BusinessArea"
PATHITEM (
PipeDelimitedValues,
[Value],
TEXT
) // Get each item from the pipe-separated list
)
// This creates a filter table using the business areas we just created.
VAR BusinessAreaFilterTable =
TREATAS (
SELECTCOLUMNS ( BusinessAreaList, "Business Area (OM)", [BusinessArea] ),
// Select the "BusinessArea" column
'FctEmployee'[Business Area (OM)] // Link it to the corresponding column in the employee table
)
// This creates a core dataset by summarizing employee information.
VAR SummarizedEmployeeData =
CALCULATETABLE (
SUMMARIZE (
'FctEmployee',
// Source table is 'FctEmployee'
'FctEmployee'[Employee Name],
// Include employee name
'FctEmployee'[EmployeeEmail],
// Include employee email
'FctEmployee'[Business Area (OM)],
// Include business area
'FctEmployee'[Region],
// Include region
'FctEmployee'[Personnel Subarea] // Include personnel subarea
),
KEEPFILTERS ( BusinessAreaFilterTable ) // Apply the filter we created earlier
)
// This selects all records from the summarized data.
VAR AllEmployeeRecords = SummarizedEmployeeData // Source from the summarized dataset
// This part runs the evaluation of the selected dataset.
EVALUATE
AllEmployeeRecords // This orders the final results by the specified columns.
ORDER BY
'FctEmployee'[Employee Name],
// First by employee name
'FctEmployee'[EmployeeEmail],
// Then by email
'FctEmployee'[Business Area (OM)],
// Then by business area
'FctEmployee'[Region],
// Then by region
'FctEmployee'[Personnel Subarea]
// Finally by personnel subareaHere is the code snippet
DEFINE
VAR __DS0FilterTable =
TREATAS({"@{triggerOutputs()?['body/DL']}"}, 'FctEmployee'[Business Area (OM)])
VAR __DS0Core =
CALCULATETABLE(
SUMMARIZE(
'FctEmployee',
'FctEmployee'[Employee Name],
'FctEmployee'[EmployeeEmail],
'FctEmployee'[Business Area (OM)],
'FctEmployee'[Region],
'FctEmployee'[Personnel Subarea]
),
KEEPFILTERS(__DS0FilterTable)
)
VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'FctEmployee'[Employee Name],
1,
'FctEmployee'[EmployeeEmail],
1,
'FctEmployee'[Business Area (OM)],
1,
'FctEmployee'[Region],
1,
'FctEmployee'[Personnel Subarea],
1
)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
'FctEmployee'[Employee Name],
'FctEmployee'[EmployeeEmail],
'FctEmployee'[Business Area (OM)],
'FctEmployee'[Region],
'FctEmployee'[Personnel Subarea]
Here is what the data looks like (A text field in a SharePoint list). It gets its values like Value1; Value2; Value3 passed from a PowerApp.
I need the user to be able to select multiple Business Areas and pass that value into the TREATAS
Hi @Albertax
Real fast 🙂 can you repost this using the Code Snippet </> , its really hard to look at a giant string
I really want to help, but I am not connecting the issue you are having to this data (from the App)
Can you, possibly show me what the data looks like? Is this data in a single column that is like JSON and you want to parse it?
Apologies, its just not connecting in my head.
If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others
Cheers
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey
#1
Michael E. Gernaey
566
Super User 2025 Season 1
#2
David_MA
516
Super User 2025 Season 1
#3
stampcoin
492
