Run Query against dataset - Multiple values from A Sharepoint List

[Albertax]

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

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]

[FLMike]

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

[Albertax]

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

[Albertax · Accepted solution]

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