cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
selimovd
Frequent Visitor

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
yashag2255
Dual Super User II
Dual Super User II

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
yashag2255
Dual Super User II
Dual Super User II

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!

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.

yashag2255
Dual Super User II
Dual Super User II

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!

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?

yashag2255
Dual Super User II
Dual Super User II

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

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,116)