Trying to Build a Power App using Power Bi dataset with Power Automate

[amycdcolbert]

I could really use some help since I've been continuously coming up against roadblocks and due to being totally new to the world of DAX, coding, and power platforms.

I'm trying to build what should be a simple app but it's turning out to be way more complicated as I've been hounding at this problem and project for the past two or three week straight.

Essentially, I'm trying to build what we're calling "a pick list" that is dynamic for our associates to use. We've got a fairly simple table visual on PowerBi that does this but we're wanting it in a PowerApp for them to use on a tablet. The table on PowerBi pulls data from an oracle database and executes a query so that what is displayed is the correct part number, part description, quantity needed, unit of measure for that quantity (ex. EA vs mm or cm), the location of the part, the step in the assembly instructions that uses each part, which department (ex. Fabrication vs Sub Assembly), which work area within that department, (ex. engine-line vs cooling mod) and which workcenter within that area (ex. Zone 1 vs Zone 5). The filters applied to the table are the serial number for the product being assembled, the department, the area, and workcenter.

I have in my mind the app I'm trying to build but it's proving to be extremely difficult due to DLPs limiting my access to oracle DB connectors. I know there is a very complex work around (it's what I've been painfully battling) but I thought I'd finally reach out for suggestions or help.

So far, I know I can use "run a query against a dataset" in power automate and I know that data can be sent where ever I want but the problem is that if I try to extract all data it's way too large (over a million rows one way and well over several million items another way). So then I've been trying to first just extract the easily distinct values (serial numbers, department, area, and workcenter) but the problem I'm facing is extracting them distinctly so that the file I send them to recognizes they are distinct values (if I try to create a csv with the "run a query" it tries to make each row match the serial number to every possibility that the serial number pops up for every department and so on). Basically, I'm trying to force the system or end file to recognize that the values for "serial number" (n=1000 +/-) are not related to the values for "department" (n=6) or the values for area (n=12), etc. etc. 

I thought that if I could create an app with a screen that gives the associate drop down options of: "Serial Number", "Department", "Area", and "Workcenter" and they press "go", the next screen shows the appropriate picklist. They can then use that picklist to pull parts in an efficient and organized manner and also have an option to check "Pulled" for each part to mark it off the list. The app will also need to reflect the most accurate and up-to-date information as there are engineering changes that may change a part or location or whatever for each serial number. There may also be new serial numbers or workcenters added, too! PowerBi can reflect those changes already.

Any suggestions? It's simple on the surface but this project is turning into quite the migraine for me. I've gone through soooo many iterations of failed power automate and power apps and I keep getting just a hair close to what I want and either there is too much or not enough or not the right data.

[lbendlin]

Neither Power BI not Power Automate are suitable for what you are trying to achieve.  Power BI is a reporting tool and has very poor data write back capabilities. Power Automate is not designed to handle the amount of data that you are dealing with.  

PowerApps sounds like it might fit the bill a bit, what with the ability to accept and process user input.  But you will have to face the fact that the majority of your logic will have to sit in the Oracle database. (Power Apps has horrible performance with large datasets and forces you into paging anyway) That means views, indexes, direct query connectors etc.  If you can't get past your DLP restrictions then you need to pull the plug on this project. There are lots of consultancy companies that would gladly develop a custom app for you.

[amycdcolbert]

I was afraid of that. Thank you. I have a meeting with a higher up corporate IT manager. Crossing my fingers I can get access granted to the Oracle connector. Literally all I need.