Microsoft Power Platform Functional Consultant Test

You are creating a Power BI report where users need to dynamically filter data based on a range of dates. The report has a Sales table with columns Date, ProductID, and Amount. You decide to use a Power Query parameter to allow users to select a start and end date, which will then filter the Sales table accordingly. The parameter is named DateRange and has a type of List. You need to write a Power Query M formula to filter the Sales table based on the selected date range. The DateRange parameter contains two dates: the first item is the start date, and the second item is the end date. How should the M formula be structured to achieve this functionality?

A: Table.SelectRows(Sales, each [Date] >= DateRange{0} and [Date] <= DateRange{1})

B: Sales{[Date] >= DateRange{0}, [Date] <= DateRange{1}}

C: Table.FilterRows(Sales, each [Date] >= List.First(DateRange) and [Date] <= List.Last(DateRange))

D: Table.SelectRows(Sales, each [Date] >= List.First(DateRange) and [Date] <= List.Last(DateRange))

E: Filter.Table(Sales, [Date] >= DateRange{0}, [Date] <= DateRange{1})

Consider the following two tables in Power BI:

We received the following error when we tried to set many-to-one cardinality between devices to users tables: “The cardinality you selected isn’t valid for this relationship”. What should we do before creating the many-to-one relationship?

Review the following Microsoft SQL Server table:

Complete the following Power Query formula used to create a custom column to replace the numbers present in last 11 characters of ISBN with 11 ‘*’. Example ‘978-0-365-51051-4’ is converted to ‘978-0-***-*****-*’

A: Text.Insert
B: Text.Replace
C: Text.Start
D: Text.End

You're tasked with building a Power Automate flow for a purchase order approval system. Here are the requirements:

1. Initiation: The flow starts when a purchase order is submitted via a SharePoint list.
2. Initial Approval: The purchase order should first be routed to the submitter's direct manager (information available in SharePoint).
3. Conditional High-Value Routing:
    - If the purchase order value is over $5000, it requires an additional approval from the department head.
    - If the value is $5000 or less, proceed directly to final processing.
4. Final Processing: Upon all approvals, update the purchase order status in the SharePoint list and send a confirmation email.

Which of the following is the valid approach?

OPTION A:
    - Initialize two string variables: 'Manager Approval' and 'Department Head Approval'.
    - 'Get manager (V2)' to store the submitter's manager email in 'Manager Approval'.
    - 'Start and wait for an approval' (Approval type: Approve/Reject - First to respond) using 'Manager Approval'.
    - 'Condition': Purchase order value is greater than 5000.
        - If yes: Set 'Department Head Approval' with department head's email and start another approval.
        - If no: Proceed to final processing steps.

OPTION B:
    - 'Get manager (V2)' to retrieve the submitter's manager email.
    - 'Start and wait for an approval' (Approval type: Approve/Reject - First to respond) using the manager's email.
    - 'Condition': Purchase order value is greater than or equal to 5000.
        - If yes: Proceed to final processing steps.
        - If no: 'Get manager (V2)' to retrieve department head's email, followed by another 'Start and wait for an approval'.

OPTION C:
    - 'Start and wait for an approval' (Approval type: Approve/Reject - Everyone must approve)
    - Add both the submitter's manager and the department head as approvers.
    - 'Condition': Purchase order value is greater than 5000.
        - If yes: Proceed to final processing steps.
        - If no: Set status to 'Rejected' in the SharePoint list.

OPTION D:
    - 'Get manager (V2)' to retrieve the submitter's manager email.
    - 'Start and wait for an approval' (Approval type: Approve/Reject - First to respond) using the manager's email.
    - Create a parallel branch.
        - Branch 1: 'Start and wait for an approval' with department head's email (retrieved from SharePoint).
        - Branch 2: 'Condition' to check if the purchase order value is greater than 5000.

OPTION E:
    - Initialize a 'Department Head Approval' string variable.
    - 'Start and wait for an approval' (Approval type: Approve/Reject - First to respond).
    - 'Condition': Purchase order value is greater than 5000.
        - If yes: Assign 'Department Head Approval' variable with department head's email (retrieved from SharePoint).
        - If no: Proceed to final processing steps.
    - 'Start and wait for an approval' (Approval type: Approve/Reject - First to respond) using the 'Department Head Approval' variable.

You are tasked with integrating Power Automate with a custom REST API to retrieve information about project tasks. The API endpoint is https://api.customdomain.com/tasks and requires an API key for authentication. The endpoint responds with a JSON payload containing an array of tasks, where each task has id, title, description, status, and dueDate. Your objective is to parse this response to filter tasks that are "In Progress" and have a dueDate within the next 7 days. The filtered tasks should then be formatted into an HTML table and sent in an email notification.

Given the JSON schema for the task object is:

Write the expression used in the 'Filter array' action to achieve the filtering criteria, and provide the schema to parse the API response in the 'Parse JSON' action. Additionally, outline the steps to format the filtered tasks into an HTML table.

A: Use the expression @equals(item()['status'], 'In Progress') && @lessOrEquals(item()['dueDate'], addDays(utcNow(), 7)) for the 'Filter array' action. In the 'Parse JSON' action, manually enter the provided JSON schema. To format into an HTML table, use a 'Select' action to transform the filtered array into HTML string elements for each task, then concatenate these strings in a 'Compose' action.

B: The filtering logic is not directly supported in Power Automate without custom code. Use an Azure Function to process and filter the API response.

C: Use the OData filter query directly on the custom API call to filter tasks by 'status' and 'dueDate' before parsing the JSON response.

D: Implement a 'Data Operations - Filter array' action with the expression item().status == 'In Progress' && item().dueDate <= addDays(utcNow(), 7) and use the JSON schema as is. For formatting, use a 'Data Operations - Compose' action to manually construct the HTML table from the filtered tasks.

E: For the 'Filter array' action, write the expression item()['status'] == 'In Progress' && item()['dueDate'] <= formatDateTime(addDays(utcNow(), 7), 'yyyy-MM-dd'). Use the provided JSON schema in the 'Parse JSON' action. For the HTML table, iterate over the filtered tasks with an 'Apply to each' action, building the HTML table string dynamically.

F: Filtering and formatting tasks as described require preprocessing the JSON response in a script before using Power Automate, as Power Automate does not support complex data manipulations.

You have been assigned to develop a Power Automate flow that streamlines the process of analyzing customer feedback collected through a web form. The feedback is initially stored in a Microsoft Forms response and includes customer ratings and open-ended comments. Your objective is to perform sentiment analysis on the comments using Cognitive Services Text Analytics, categorize the feedback based on sentiment (Positive, Neutral, Negative), and then summarize the results in a Power BI report for further analysis and visualization.

The flow should automatically trigger upon new form submission, extract the comment from the form response, perform sentiment analysis using the Text Analytics API, categorize the feedback, and store the results in a SQL database table named "FeedbackAnalysis". The table has columns for FeedbackID, Comment, SentimentScore, and SentimentCategory.

Given this scenario, how should you configure your flow to efficiently handle the feedback analysis, categorization, and data storage for Power BI reporting?

A: Implement a flow that triggers with the 'When an item is created' action in a SharePoint list, assuming a separate process transfers form responses to the list. For each new item, extract the comment and use the 'Detect Sentiment' action from the Text Analytics connector. After categorizing the sentiment, store the results in the "FeedbackAnalysis" SQL table and configure Power BI to visualize the data.

B: Manually trigger the flow for batches of form responses. Aggregate the comments in a 'Data Operations - Compose' action. Use the Text Analytics connector's 'Detect Sentiment' action for batch processing of comments. Store the sentiment analysis results directly in a SharePoint list, then use Power BI to import the SharePoint list data for reporting.

C: Set up a scheduled flow to run at the end of each day, collecting all the day's form responses using a 'List records' action. For each comment, use the 'Detect Sentiment' action from the Text Analytics connector. Categorize the feedback based on the sentiment score thresholds defined in a 'Switch' control. Insert categorized feedback into the "FeedbackAnalysis" table and refresh a Power BI dataset for real-time reporting.

D: Use a 'When a new response is submitted' trigger for Microsoft Forms. Extract the comment using 'Get response details'. Utilize the pre-built Text Analytics connector's 'Detect Sentiment' action for sentiment analysis. Determine the feedback category based on sentiment score thresholds using a 'Condition' control. Insert the feedback, sentiment score, and category into the "FeedbackAnalysis" SQL table. Integrate the SQL data with Power BI for dynamic reporting.

E: Configure a flow that triggers on a weekly basis, using a 'Get responses' action to collect feedback from Microsoft Forms. Manually send each comment to the Text Analytics API using custom code in an Azure Function. Store the analysis results in a Cosmos DB, and use Azure Synapse to process the data for Power BI reporting.