How to join data when item is created in SQL?

[selimovd]

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

[yashag2255]

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]

At the moment my flow looks like this:

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]

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]

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	DateTime	SalesAmount in USD
12	2020-01-20 13:30	140

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

ProductID	Product description	Product color
11	Race bike Tour de France	yellow
12	Mountain Bike	black
13	Electro Bike	green

To Power BI I want to push the following:

ProductID	Product description	Product color	DateTime	SalesAmount in USD
12	Mountain Bike	black	2020-01-20 13:30	140

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 · Accepted solution]

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!

[selimovd]

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 😉