Scenario<\/strong><\/p>\nLet\u2019s take a closer look at how we can use this new capability to enhance our data extraction capabilities.<\/p>\n
In this scenario, we have an Apartment Rental company called Contoso Apartments. Naturally, customers will inform the main office when there are maintenance issues across their many properties. Much like any Work Order system, data is organized in multiple tables including Customers, Apartments <\/strong>and Work Orders<\/strong>. The Customer Service representatives are very interested in keeping their customers happy and want to proactively ensure that customers are content. If they are not, they want to quickly course-correct to avoid them leaving to live somewhere else.<\/p>\nWe can monitor customer sentiment by \u2018mashing\u2019 up our Customer, Apartment and Work Order data from Power Query. When we do this, we have aggregated data that can be passed to a sentiment analysis tool. When we detect that a customer is unhappy, we can then publish a message to Microsoft Teams where a customer service rep can follow-up with the customer.<\/p>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/9-solution.png\")
<\/p>\n
To build this solution we will perform the following:<\/p>\n
\n- We will add a Recurrence<\/strong> trigger that will run every day<\/li>\n
- Next, we will locate our Transform data using Power Query<\/strong> action which is provided as part of the SQL Server <\/strong>connector.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/1-addpq.png\")
<\/p>\n
\n- To connect to SQL Server, we need to create a Connection<\/strong> that includes a SQL Server name, Database name, Username <\/strong>and Password<\/strong>.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/1-bAddConnection.png\")
<\/p>\n
\n- With our connection established, we can now create a Power Query query<\/strong>.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/2-createQuery.png\")
<\/p>\n
\n- Our next step is we need to select the tables that we would like to include in our mash-up. In this case we are going to select Customers, Apartments <\/strong>and Work Orders<\/strong>.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/3-tables.png\")
<\/p>\n
\n- We want to join these different tables so that we have enriched Work Order data that includes Customer and Apartment related data for our Work Orders. To do this, we will click on Combine tables <\/strong>\u00a0and then select Merge queries as new.<\/strong><\/li>\n<\/ul>\n
\u200b![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/4-mergeQueries.png\")
<\/strong><\/p>\n\n- When we merge, we need to select the type of join that we want to use. In this case we will select a Left Join<\/strong> and declare our Work Order table as our core table that we want to enrich with Customer and Apartment data.<\/li>\n<\/ul>\n
Note: <\/strong>In this scenario, I ran the Merge <\/strong>twice. Once with Work Orders and Customers and then once again with my (WorkOrders and Customers) + Apartment.<\/p>\n![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/5-merge.png\")
<\/p>\n
\n- Once we have merged our tables, we can now trim our dataset by only including the columns that we need.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/6-chooseColumns.png\")
<\/p>\n
\n- Before we configure the rest of our Flow, we do need to declare our new aggregated query as the query we will Enable Load<\/strong> for.<\/li>\n<\/ul>\n
Note:<\/strong> At this time, you can only enable 1 query that will return back to Flow. However, as we have discovered we can merge multiple queries into a single query for our use.<\/p>\n![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/8-EnableLoad.png\")
<\/p>\n
\n- With our Power Query query configured, we can now use the result set and dynamic content, much like we can do with other connectors. In our use case, what we will do with our result set is loop through each record returned and send the Work Order Comments from the customer through the Azure Cognitive Services sentiment analysis API.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/7-DetectSentiment.png\")
<\/p>\n
\u00a0<\/p>\n
\n- Next, we will evaluate the sentiment returned to see if it is less than .4 (which is really bad). When this occurs, we will add related Apartment, Customer and Work Order information with this sentiment value and add to an Array. After we have iterated through all of these recent Work Orders, we will then check the length of the array to see if we have records. If we do have records, we will convert this Array to an HTML table which we can then publish to a Microsoft Teams Channel.<\/li>\n<\/ul>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/10-RestOfFlow.png\")
<\/p>\n
Testing<\/strong><\/p>\nWe can now go and run this flow from within the Microsoft Flow maker portal or by calling it from the Microsoft Teams Flow Bot. Once the flow runs, it will publish the results of our flow in Microsoft Teams. This allows the Customer Service channel to target customers who are unhappy without performing a lot of data exploration.<\/p>\n
![\"\"](\"https:\/\/www.microsoft.com\/en-us\/power-platform\/blog\/wp-content\/uploads\/2024\/06\/11-Testing-1.png\")
<\/p>\n
\u00a0<\/p>\n
Features<\/strong><\/p>\n\n- Only SQL Server is supported as a data source. This is deliberate in our first release as we do not want to expose additional data sources that are not protected by Microsoft Flow Data Loss Prevention (DLP) policies. We do want to include additional data sources, but those will be future investments.<\/li>\n
- We do throttle Power Query Online usage based upon:\n