Automatically Add Missing Data Back To Your Tables! | Run A Query Against A Dataset

Join this channel membership to get access to all the recorded bites as they become available:
/ @curbalen
Here you can download all the pbix files: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Mastodon ► @[e-mail]
Twitter ► @curbalen
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE

[@TazmanianFlo]

Hi Ruth, after the "Run a query ..." action you can simply add an "Apply to each" action and set the "First table rows" to be looped over. Within this apply-to-each-loop you can access the current row using the "item()" expression. In your case it should be item()['Orders[Product nr]']. To debug, simply add a Compose action within the loop and use item() for the expression of compose - this should output the current row.
To get the number of rows returned from PBI in order to check if there are any rows you can use this expression (no need for separate query) : length(outputs('Run_a_query_against_a_dataset')?['body/firstTableRows'])
I always try to solve this problem directly within Power Query if possible. Detect missing master data (i.e. anti join, remove duplicates ...), append these as "dummy" master data. But I see cases where Power Query might not be the way to go.

[@CurbalEN]

Thanks ! Will give it a go!

[@HachiAdachi]

Thank you, Ruth! 👍
Counterpoint: I personally like the 'New Designer' in Power Automate because I feel that:
1) the new designer is easier to navigate around a long flow with bunch of conditions, parallel steps, etc.
2) the expression/query window is expandable and much easier to input
3) Renaming a process doesn't require you to go through a right-click menu
I agree with you that Copilot in Power Automate is not ready for prime time (at least it wasn't when I last tried to use it). Hopefully, it will improve soon... 🤞

[@CurbalEN]

I couldnt work with it when the panes were on each side of my wide screen, but if they change that I will give it another try :)
Thanks for the feedback!

[@BrettvanGelder]

Would it not work to add a Parse JSON step after the Power BI query and then use the output from that? You can then do a For Each action which Power Automate will usually force on you anyway.

[@justinmitchell476]

How would you do this? cause I am having a null issue

[@CurbalEN]

I did that but i got the entire json string instead of only the value. I wansnt able to strip it from the name :(

[@jackiechantamil1302]

Hi I wanted to do a forecast for 53 weeks 450 products for 6 regions for each region and product I should apply different percentage of growth based on region week and product group it should be interactive and i should be able apply all different percentage for each product and week what's the best way to do it please