Power BI

[gemma-the-husky]

Someone at a client mentioned Power BI yesterday.

Is that a useful addition to an Access based system.
Does anyone have any useful explanatory links please?

 

[theDBguy]

Power BI is used to create dashboard reports. It's usually used with online data sources rather than with Access. What was the context when it was mentioned?

 

[gemma-the-husky]

@theDBguy
Thanks Guy. I sent you a PM.

 

[plog]

I wouldn't call it an addition to Access, closer to a replacement, but not really. Helpful huh?

Part of Access is a database, BI is not. BI makes it easy to connect to various data sources--SQL Server, Oracle, Excel, even Access databases. Part of Access is making reports and forms, that is the part that BI replaces. You can easily build web based forms/reports to interact with all those various data sources using BI.

If I had a self contained Access system with all the tables, forms and reports I needed and everyone was familiar with that, I wouldn't use BI. If you were building a system and needed some people who may not have Access to use it or just wanted it on the web, that is when I would use BI. Most likely it wouldn't be an addition to Access but a replacement--because why build/maintain 2 systems?

 

[MadPiet]

Someone at a client mentioned Power BI yesterday.

Is that a useful addition to an Access based system.
Does anyone have any useful explanatory links please?

PowerBI is helpful for interactive graphs and visuals, which is something that Access has never been good at. PowerBI wouldn't replace Access/SQL Server, but would work with it. PowerBI is helpful for drilling into totals and seeing what makes them up etc. The kicker is that PowerBI doesn't use a standard 3NF/OLTP database like Access databases usually are, but a data warehouse where each fact is at the center of a "star" and all of its related dimensions (tables with keys and descriptive columns) are around it. Then some facts may share some of the dimensions. (Yeah, it's complicated!) But it lets you visualize huge amounts of information pretty easily.

Does it work well with a database? Yes, but PowerBI can handle a lot more records than Access can. So I usually use SQL Server for that. Then I can write obscene stored procedures if I need to that twist my data into a format that is useful in PowerBI and PowerBI can consume it. (and PowerBI can read tons of different data sources... far more than Access. Not that long ago, I wrote a PowerQuery that read and parsed a table from a PDF of an SSRS tabular report. (PowerQuery is super cool!) But to answer your question - I think it's a "both and" thing. Database for storage and structure, and then maybe a different structure for the data warehouse bit (but you can model all that in PowerBI if you want to.)

Sorry, it's not a super easy question to answer. There's a lot to PowerBI. Sorta like Access, there's the tables, the relationships... and then there's the DAX which can be really hard to get your head around. But if you can, the stuff you can do with it is pretty interesting.

What did you want to use PowerBI for? In general terms, what's your database (or databases) about that you want to analyze with PowerBI?

Maybe look at AdventureWorks (zzzzzzz) and then the data warehouse version (AdventureWorks DW). You can download them both from github and play with them, but they're in SQL Server. (I know Rob Collie had a baby version of AdventureWorks DW in Access, because I used it in a course I took. His book is like $30 and a pretty good intro to the subject.

 

[gemma-the-husky]

Thanks all for the responses.

@plog

I think that sort of nails it. I had wondered whether Power BI could replace an Access database of itself, but it doesn't sound like it can.

We currently generate reports etc using a dashboard/menu system to select pre-defined report examples. The access users aren't skilled enough to write their own queries.

I wondered whether Power BI might make it easier to develop ad-hoc queries. However I expect it may not be any simple for an untrained Power BI user to extract and format data, any more than an unskilled Access user. Is that a fair summary?

 

[Minty]

I looked at in anger properly about 5 years ago. I didn't find it very intuitive, but I could see how an untrained user could probably get something useful from it depending on their ability to "click and see what happens" as the desktop version UI (was) is quite user friendly.

IMHO, it certainly can't replace an Access database.

 

[NauticalGent]

When I was asked what it would take to fully replace Access with PowerApps/PowerBI, etc...I looked into what it would take. Before I knew about ChatGPT, I had to do some extensive GDB-ing...

Dave's question prompted me to ask, here it is:

Good morning! If you're considering replacing Microsoft Access with Power Platform apps, you're looking at a shift from a traditional desktop database model to a more modern, scalable, cloud-based solution. Here’s what you'd need and how the main components of the Power Platform can help:

---

1. Tools You’ll Need​

Tool	Purpose
Power Apps	To create custom apps (can mimic your Access forms and navigation).
Dataverse (or SharePoint Lists or SQL)	Acts as the backend database (replacing Access tables).
Power Automate	To replace macros or VBA with modern workflows and automation.
Power BI	For advanced reporting, analytics, and replacing complex Access reports.

---

2. Structure Comparison​

Access Component	Power Platform Equivalent
Tables	Dataverse / SharePoint / SQL
Queries	Power Automate / Power Apps logic / Dataverse Views
Forms	Power Apps Screens
Reports	Power BI dashboards and reports
Macros/VBA	Power Automate Flows

---

3. Licensing​

To build and run apps with Dataverse, you may need:

• Power Apps per user or per app plan (for Dataverse access)
• Microsoft 365 license (if using SharePoint instead of Dataverse)
• Power BI Pro (if using Power BI for reporting)

---

4. Considerations​

• Dataverse is ideal for more structured, relational data (like Access). It has better performance, security, and integration capabilities.
• SharePoint Lists are simpler and good for basic needs.
• SQL Azure is best for very complex or large-scale backends.
• You may need to rebuild logic (queries/macros) using modern tools—this is a one-time lift, but can take some planning.

---

Would you like help designing a migration plan or mapping your current Access features to Power Platform components?

I had come to a similar conclusion but it took my MUCH longer to get there and Chatty summed it up better then I did. This course of action was too cost prohibitive, the learning curve too steep. Since Access was already bought and paid for, they moved on.

With the Government workforce returning to the office, the "need" for a web-based app diminished.

Access is Dead, Long Live Access...

 

[NauticalGent]

I looked at in anger properly about 5 years ago.

 

[GPGeorge]

I looked at in anger properly about 5 years ago. I didn't find it very intuitive, but I could see how an untrained user could probably get something useful from it depending on their ability to "click and see what happens" as the desktop version UI (was) is quite user friendly.

IMHO, it certainly can't replace an Access database.

I've been using PowerBI for graphical reports for years now. As others have said, it's not even remotely an Access replacement. It's read-only. No data entry is possible via PowerBI. Charts and graphs can be made interactive, though. It's got a great charting engine.

It's definitely not intuitive for Access developers steeped in bound forms, SQL queries and VBA. But in it's own context, as a reporting tool, it's pretty powerful.

I started to say that the biggest drawback for Access developers I see is that it can't connect to accdbs, but I just tried and find that it can, indeed connect to accdbs. Either it didn't used to be possible, or I was flat wrong. But the Desktop version of PowerBI can indeed connect to an accdb. I need to pursue that now that I've been proven wrong.

I would say that Dave is right about the skills required. I remember spending hours and hours configuring and futzing about with data sources to get what I wanted.

What I would say is this. If you've been using Excel for charts and graphs of your Access data, PowerBI worth considering in that role.

 

[GPGeorge]

When I was asked what it would take to fully replace Access with PowerApps/PowerBI, etc...I looked into what it would take. Before I knew about ChatGPT, I had to do some extensive GDB-ing...

Dave's question prompted me to ask, here it is:



I had come to a similar conclusion but it took my MUCH longer to get there and Chatty summed it up better then I did. This course of action was too cost prohibitive, the learning curve too steep. Since Access was already bought and paid for, they moved on.

With the Government workforce returning to the office, the "need" for a web-based app diminished.

Access is Dead, Long Live Access...

I gave a presentation last Thursday to my Access Pacific User Group in which I talked about using PowerApps and SQL Server Stored Procedures (and tables) to replicate the Northwind Developers Edition template. It'll be live on AUG's YouTube Channel tomorrow morning.

I don't foresee PowerApps "replacing" Access at any point in the near future. However, with the appropriate set of skills, any professional Access developer can add the remote, or mobile, capabilities of PowerApps to their toolkit and move right on ahead.

Those skills, I think, include:

• Access/VBA
• SQL Server/Stored Procedures
• PowerApps interface design

My short term goal is to finish up creating my PowerApps version of Northwind Developers Edition, at least with basic CRUD and Data validation using exclusively Stored Procedures. Long term, I'd like to add other features if possible. The biggest hurdle is going to be reporting, and for that, I might end up adding PowerBI to the project. I know printing to a local printer is going to be a challenge.

Notice that nowhere in there did I mention Dataverse. There two reasons for that. One, it's not a viable data source for an Access interface due to performance problems. Two, it might well demand incorporating Power Automate to handle the functions I'm using SQL Server Stored Procs for. I'm not entirely sure that's even doable. Plus it's another new skillset.

I've solicited feedback from attendees at that meeting and I'd love to hear from others, either in the comments on that YouTube video or directly via email.

I intend to push this as far as I can go because it fascinates me and because I'm always eager to see how ingenious and resourceful Access developers can be.

After all, as Jeff Boyce famously said, "How hard can it be?"

 

[MadPiet]

Thanks all for the responses.

@plog

I think that sort of nails it. I had wondered whether Power BI could replace an Access database of itself, but it doesn't sound like it can.

We currently generate reports etc using a dashboard/menu system to select pre-defined report examples. The access users aren't skilled enough to write their own queries.

I wondered whether Power BI might make it easier to develop ad-hoc queries. However I expect it may not be any simple for an untrained Power BI user to extract and format data, any more than an unskilled Access user. Is that a fair summary?

"I wondered whether Power BI might make it easier to develop ad-hoc queries. However I expect it may not be any simple for an untrained Power BI user to extract and format data, any more than an unskilled Access user. Is that a fair summary?"

Not quite sure how to put this. Comparing DAX and Analysis Services Tabular to Access is problematic, in my opinion. Tabular databases require star schemas... there's not really an option to have long relationship chains and have it work the way you can in a relational database... For example, if you wanted to answer a question like "Which vendors do some subset of the customer base usually buy from?" where the relationship chain is something like Vendor--(1,M)--Product--(1,M)--Sales--(M,1)--Customer

In tabular databases, tables are denormalized like mad. There are fact tables, which consist of foreign keys to dimension tables and then numeric columns that are aggregated, and there are dimension tables, which consist of a primary key and then a collection of columns that users can filter and/or aggregate by. So PowerBI is for *reporting*/analysis.

Yes, you can write queries in DAX, but soooo much easier in your database. (there's a video on youtube where someone figures out how to enable writeback and it's utterly mindboggling). So, to answer your question... would it replace Access? Not a chance. None. PowerBI is great at data transformation and reading from tons of sources, and visualization. The rest is pretty awful.

PowerQuery would be amazing if you could use it on the desktop and have "writer" methods. the closest you can get (that I know of) is to use Excel to run the PowerQueries and then import to Access from there.

 

[GPGeorge]

I gave a presentation last Thursday to my Access Pacific User Group in which I talked about using PowerApps and SQL Server Stored Procedures (and tables) to replicate the Northwind Developers Edition template. It'll be live on AUG's YouTube Channel tomorrow morning.

I don't foresee PowerApps "replacing" Access at any point in the near future. However, with the appropriate set of skills, any professional Access developer can add the remote, or mobile, capabilities of PowerApps to their toolkit and move right on ahead.

Those skills, I think, include:

• Access/VBA
• SQL Server/Stored Procedures
• PowerApps interface design

My short term goal is to finish up creating my PowerApps version of Northwind Developers Edition, at least with basic CRUD and Data validation using exclusively Stored Procedures. Long term, I'd like to add other features if possible. The biggest hurdle is going to be reporting, and for that, I might end up adding PowerBI to the project. I know printing to a local printer is going to be a challenge.

Notice that nowhere in there did I mention Dataverse. There two reasons for that. One, it's not a viable data source for an Access interface due to performance problems. Two, it might well demand incorporating Power Automate to handle the functions I'm using SQL Server Stored Procs for. I'm not entirely sure that's even doable. Plus it's another new skillset.

I've solicited feedback from attendees at that meeting and I'd love to hear from others, either in the comments on that YouTube video or directly via email.

I intend to push this as far as I can go because it fascinates me and because I'm always eager to see how ingenious and resourceful Access developers can be.

After all, as Jeff Boyce famously said, "How hard can it be?"

The video is live.

Ultimately, my goal fantasy would be to have a set of stored procedures that handles CRUD and data validation tasks, leaving the UI management tasks to whatever Interface application is set up and linked to it.

It obviously becomes an unbound interface, which for Access is not the ideal situation. The trade-off, though is that any interface, from .net to PowerApps to Access needs only to know which stored procedures to call, what parameters to pass in, and what outputs to expect back.

I really would love feedback on the design approach I am proposing.

 

[CJ_London]

I wrote a PowerQuery that read and parsed a table from a PDF of an SSRS tabular report. (PowerQuery is super cool!

I currently use pdftotext to convert a pdf to a text file - pdfs are typically invoice type documents. Works well be a bit slow. I can use word which is faster but it only returns a single column of text so loses the spacial formatting. Could power query be a good compromise? Faster and retain spacial formatting?

 

[MadPiet]

CJ, if (and it's a big if!!) your PDF contains tables, and you want only them, then PowerQuery is great. If you want something else, then not so much. PowerQuery basically only reads the tables and then removes all the formatting (or tries to).

If you've used Get Data in Excel, you've used PowerQuery. The thing that drives me nuts about PowerQuery is that, unlike Python (more specifically, Pandas), PowerQuery doesn't expose writer methods. Take CSVs in Pandas, for example: Pandas has pd.read_csv(file) to read the file into a dataframe and then pd.to_csv(file) to write a dataframe to a CSV file. But for some super annoying reason, MSFT decided that they didn't want people using PowerQuery as a sort of "data" version of GraphicConverter... to convert a file of Type1 to Type2, so they left the writers either out or unexposed (so there are not callable public methods for that functionality). Python, or Pandas, has a read method and a write method for every file type. (I just wish it could call the black box that is PowerQuery, so you could create a data "stream" in Python, send the data through a PowerQuery, and then write it to some other format, instead of being stuck with it just in memory. The only way to write it to another file type is to pass it through Excel, and then let Excel save the results... works, but super annoying.

Do you have an example (that's not confidential) that you could share of what you want to do? I don't think PowerQuery knows anything about spatial formatting. It's just reading text.

 

[CJ_London]

Do you have an example (that's not confidential) that you could share of what you want to do?

It’s simply a pdf invoice - it will typically have an invoice header section - number, date, order, customer id, one or more address sections (invoice to, delivered to) an items section - might be a text description, might be product, qty, price, value, vat, an invoice summary section - total, due date etc. payment terms, supplier registered office and so on. - look at any of the invoices you receive.

I want to extract all that information into a text file in basically the same layout as presented in the pdf so it is readable by the user. PDFtotext does that very well but takes several seconds. Word is faster- say a second but does not preserve the horizontal layout. It sounds like power query at best would be no better than word

The objective is to automate the creation of transaction records from pdfs to save manual input. There are typically 2-300 pdfs in many different layouts to process each month. Current process works well, just looking to see if I can make it faster. Manual input takes around 16 hours, this process cuts it down to about an hour - if word worked as I wanted, it would be about 5 minutes.

It’s that usual thing 20% of the effort for 80% functionality. The other 20% of functionality takes 80% of the effort

 

[MadPiet]

Oh, okay. This is what I would do...
use Excel. Data > Get Data > PDF...
then you'll be prompted for a file. The hard part is when PowerQuery cannot easily parse the table. That's not to say that you can't use PowerQuery to do it, but it takes some knowledge of M to do it. The reason I would use Excel is that PowerBI provides no way to "send" the parsed table anywhere.

The "many different layouts" part could be a problem, but if each PDF has a table with the same number of columns then you should be able to do it. Chandeep Chhabra has some videos on handling inconsistent column names if you need to do that. If the data isn't confidential (not likely!), you can send me one and I can see if I can use PowerQuery to parse it.

"The objective is to automate the creation of transaction records from pdfs to save manual input" -- I'd put all the PDFs in the same folder, use a folder source, filter for PDF files, and then the "Sample file" will be the template PowerQuery and the code will loop over all the PDFs in the folder, run the Sample File PowerQuery and append them all. Then when that's done, just import that into your database. (The only part I haven't worked out is how to force a Requery so that Excel reruns the PowerQueries. (but you could do that in VBA no problem).

 

[CJ_London]

if each PDF has a table with the same number of columns then you should be able to do it.

Unfortunately they don’t - could be 1-3 addresses, either in a table or not. For items/products, could be 1-5 columns.

At the moment I use an ado disconnected recordset. Documents come as a variety of file types- excel, word, txt, mht and pdf (and image). But predominantly pdf.

The ado recordset is parsed out into a richtext control where users can define a profile by selecting a ‘key phrase’ and an offset to the required data. The profile is saved and and as new documents arrive the profiles are parsed to find a match. If a match is found the data is extracted. There is a balancing calculation and if the extracted data is balanced then the data can be imported otherwise the user can review the profile and adjust - typically for new products. If no match is found, then the user can create one.

To define a profile - you have a key phrase, an identifier and file type

this one happens to be a mht rather than a pdf. If it came as a pdf, the layout would be different

Next you identify the key data required

and finally product information - this can vary and is usually the reason for not balancing - a new product needs adding

These are presented in a richtext control so user can see what has been selected and from which the user can modify the profile

so the balance calculation is 2 * 7.49 and set against the invoice total of £14.98. So an entry can be created

Hope that makes sense!

 

[MadPiet]

if it's confidential data, I don't think I can help. but if it's not, you could send me a message with one or two PDFs (oh, and the expected output... could be an explanation), I could see if PowerQuery would work any better.

 

[CJ_London]

I should have updated - I found a way to make pdfToText work significantly faster (<0.5 seconds). I originally had it set up to work in memory to return stdOut. But found if I use it to write to a temporary text file, then open the text file to get the text it is much faster. I had wrongly assumed that keeping it in memory would be faster than requiring disk I/O