Run Query against dataset - Multiple values from A Sharepoint List

(1) Share

Report

Posted on by Albertax

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.

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],

'FctEmployee'[EmployeeEmail],

'FctEmployee'[Business Area (OM)],

'FctEmployee'[Region],

'FctEmployee'[Personnel Subarea],

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

'FctEmployee'[Employee Name],

'FctEmployee'[EmployeeEmail],

'FctEmployee'[Business Area (OM)],

'FctEmployee'[Region],

'FctEmployee'[Personnel Subarea]

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Michael E. Gernaey 53,963 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Albertax 111 on at

Like
a
(1)

Report
Copy link

Link copied!
Here 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

Was this reply helpful? Yes No
Verified answer

Albertax 111 on at

Like
a
(1)

Report
Copy link

Link copied!
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

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 subarea

Was this reply helpful? Yes No