cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
selimovd
Level: Powered On

How to join data when item is created in SQL?

Hello everyone,

 

I have a kind of easy flow. When a new row is created in Azure SQL Server I have to add some new information from another table (join) and then add it to a Power BI dataset.

 

The trigger works fine and also to push it to power bi. But how can I join the information from another SQL Server table to my data?

I searched and tried some stuff but I can't figure it out.

 

Thank you and best regards

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: How to join data when item is created in SQL?

Hi @selimovd 

 

I get it now. In this case, you will have to add an action to get the row from the other table. Assuming that the Product ID will be unique, you can use the list row action and then in the filter query add the query as ProductID eq 'selectTheProductIDFromDynamicSelector' and then now you should be able to get the row from the other table. Now you can access the other columns by using the first() function to access the first element returned in the list rows action. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

6 REPLIES 6
Dual Super User
Dual Super User

Re: How to join data when item is created in SQL?

Hey @selimovd 

 

Can you share the screenshot of the flow that you have built so far? 

 

Are you trying to join data items or perform any calculations on certain attributes? If you can share more information, we might be able to better assist you with this. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

selimovd
Level: Powered On

Re: How to join data when item is created in SQL?

At the moment my flow looks like this:

PowerAutomate.png

 

In my fact table I have an ID like ProductID and when a new sales is done there will be a new row.

 

My problem is now that in Power BI I don't want to see that we sold another ProductID 12, I want to know that we sold "Product XYZ". So I need a join with the product information table. So at the end in Power BI it should show that we sold now another "Product XYZ" instead of ProductID 12.

 

I didn't find any way how I can add the information from the product table which product the ProductID 12 is.

Dual Super User
Dual Super User

Re: How to join data when item is created in SQL?

Hey @selimovd 

 

I am still trying to understand the scenario. So as I understand you get a column value as ProductID 12 and in place of this, you want to send Product XYZ. In this case, you can add a compose action after the trigger to compose that value and maybe pass that into the powerbi action. Can you share an expanded screenshot of the PowerBI action? 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

selimovd
Level: Powered On

Re: How to join data when item is created in SQL?

Hey @yashag2255 ,

 

thank you for the reply. So I try to explain the example better. I have the table "Fact Sales" that triggers the action.

Let's say there was an insert of a new row that triggers the action:

ProductID DateTimeSalesAmount in USD
122020-01-20 13:30140

 

I have another table "Product details" with my product details:

ProductIDProduct descriptionProduct color
11Race bike Tour de Franceyellow
12Mountain Bikeblack
13Electro Bikegreen

 

To Power BI I want to push the following:

ProductID Product descriptionProduct colorDateTimeSalesAmount in USD
12Mountain Bikeblack2020-01-20 13:30140

 

Like this I can make a Power BI report and see that for example we sold another 3 black Mountain bikes in the last hour. So in SQL language this would be a join between the first and the second table.

 

In flow/power automate I was able to send my first table with the trigger directly to Power BI. But I didn't figure out how to add the information from the second table (product description and product color) to my Power BI data set. This I need because nobody will know that ProductID 12 is a black Mountain Bike. So I need to join this information in Flow to the original table that I can do my analysis in Power BI.

 

How can I join the second table to the first one in flow?

Dual Super User
Dual Super User

Re: How to join data when item is created in SQL?

Hi @selimovd 

 

I get it now. In this case, you will have to add an action to get the row from the other table. Assuming that the Product ID will be unique, you can use the list row action and then in the filter query add the query as ProductID eq 'selectTheProductIDFromDynamicSelector' and then now you should be able to get the row from the other table. Now you can access the other columns by using the first() function to access the first element returned in the list rows action. 

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

selimovd
Level: Powered On

Re: How to join data when item is created in SQL?

Thank you  @yashag2255 

I added a "Get rows" from SQL Server and used my join parameters for the "Filter Query". This worked now.

 

I slowly figure out how this stuff is working 😉 

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,647)