Power BI Gateways - Simply Explained

Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. Why your reports sometimes stop updating. Picture a finance team opening their power BI dashboard on a Monday morning and finding numbers from last Thursday. The data was fine, but the machine holding the connection had gone to sleep. That connection runs through a power BI gateway, a small app that carries data between a private network and the Microsoft Cloud. You might think every report needs one, but plenty of data sources connect without a gateway at all. Three kinds of gateways exist, and picking the wrong one is where most people get stuck. By the end, you'll know which one fits your setup. What a gateway actually is. A power BI gateway is software you install on a computer inside your own network. Its whole job is to carry data between that network and the Microsoft cloud. And it does that one thing well. It isn't a box you rack in a data center, and it isn't something Microsoft runs on your behalf. It's a small windows program that someone on your team installs signs into with a work account, and then leaves running. The name doesn't help much here, because gateway sounds like heavy networking hardware or a service that lives entirely in Microsoft's hands. What you actually get is an app sitting on a machine you control, and it moves data back and forth while you get on with building reports. When a report needs fresh numbers from a database in your office, this app is the piece that goes and gets them. A gateway serves far more than power BI, which surprises most people the first time they hear it. The same app can carry data for power apps, Power Automate, Microsoft Fabric, Azure Analysis Services, Logic Apps, and Azure Data Factory. So the gateway you set up for one report often ends up doing work for tools you haven't touched yet. That's why it's worth understanding properly. Instead of copying a setup from a blog and hoping it fits. It also helps to know what kind of work the gateway does, because not all refreshes are the same. When a report imports its data, the gateway pulls the rows from your source and hands them up to the cloud, where power BI stores them for speed. When a report uses a live connection or direct query instead, the gateway barely stores anything. It passes each question through to your source and brings back only the answer. The first style moves a lot of data once, and the second moves small answers constantly, So the difference shapes how much machine you need underneath it. There's good news if you're worried about cost. Microsoft places no licensing restrictions on installing and registering a gateway. So the software itself is free to run. What you pay for is the machine it lives on, and whatever power BI or fabric license your reports already need. And that's a smaller bill than most beginners expect. Why your data can't just float into the cloud. The problem a gateway solves is simpler than it sounds. Power by itself lives in Microsoft's cloud, and that service can reach almost anything on the public internet. What it can't do is reach a database sitting inside your office network. Your network is private on purpose, which is exactly why the cloud service can't walk in and help itself to your data. The gateway fixes that by doing all of the connecting from your side, it reaches out to Microsoft and Microsoft answers down the same connection your gateway opened. You never have to punch a hole in your firewall for the cloud to reach in. That detail is the one people worry about most, and it works in your favor. Nothing outside your network starts a conversation with your gateway because your gateway always starts the conversation first. Most beginners reach for gateway before they check whether they need one. You don't always need a gateway. And adding one when you don't just gives you another machine to look after. If your data already lives in a cloud service that power BI can reach directly, like Azure SQL database, you can usually connect with no gateway at all. The gateway earns its place when your data sits behind a firewall, behind a virtual private network, or inside a virtual network in Azure, take two teams that build the same kinds of reports. One team keeps its sales data on a SQL server in the back office, so their reports need a gateway to pull that data up into the cloud. The other team already moved its data into Azure SQL database, so their reports connect straight to the source. These two teams build the same reports on the same cloud service. And the only real difference is where the data lives. If your first instinct is to worry that a gateway opens your network to the outside world, you're not alone, and the design works the opposite way. The gateway reaches out on its own, so you never open an inbound door into your network. That's the part people find hardest to believe until they see it working. And it's the reason a gateway can sit safely inside a network that stays closed to everything else. The round trip your data takes when a refresh runs the data takes a short round trip. Knowing the steps makes the whole thing feel far less mysterious. The cloud service starts by building the query it needs, along with the credentials for your data source. It encrypts those credentials before they leave, and the whole package waits in a queue for your gateway to collect it. Your gateway checks in on a regular rhythm, which is the trick that makes the whole design secure. It connects out to a piece of Microsoft's infrastructure called Azure Relay, picks up whatever is waiting, and then decrypts the credentials on your own machine. Nothing reaches into your network because your gateway is the only side that ever dials out that rhythm is also why the gateway has to be online when a refresh is due. If the machine is off or asleep, there's nobody there to collect the request. So the refresh sits and eventually fails, and the report keeps showing whatever it showed last time. The gateway isn't a background service that Microsoft wakes up for you, and it can't catch up on a request while it's offline. From there, the gateway connects to your data source, sends the query, and waits for the answer. When the results come back, the gateway carries them back up to the cloud service and the report updates with fresh numbers. The whole trip usually happens in seconds, and it runs the same way. Whether you're refreshing one small table or a year of transactions. One detail matters when your data gets big. If a refresh returns a large batch of rows, the gateway parks that data on its own machine until the full result arrives and then sends it up in one go. Microsoft calls this spooling, and it's the reason a solid state drive on the gateway machine is worth having. All of this traffic is encrypted as it travels, and only TLS one point two connections are accepted. If it helps, think of the gateway as a courier that checks the depot every few seconds and only ever leaves the building. It picks up the request, delivers it to the right desk, and brings the answer back. And it never props a door open for strangers. That single habit is why the gateway is safe to run inside a network that otherwise stays closed. The flow works the same no matter which gateway you run, but the machine behind it isn't the same at all. The three kinds of gateway. Power BI gives you three kinds of gateway, and the one you pick changes how your whole team works. The first is the standard gateway, which is shared and managed centrally. So one installation can serve many people and many data sources at once. It supports the heavier report modes and it can run as a cluster for extra reliability. Microsoft recommends it for anything a team depends on. The second is the personal gateway, and the name tells you most of what you need to know. It works for exactly one user. It only talks to power BI and nobody else can share it. If you're the only person building reports and you just need your own refreshes to run, it can be enough and it's the quickest one to set up. The third is the virtual network gateway and this one is a different animal. Microsoft runs it for you as a managed service, so there's nothing to install and no machine to keep switched on. It connects power BI and fabric to data sitting inside an Azure Virtual Network, which is where much of the modern data ends up. Setting up the standard gateway also gives you a recovery key, which lets you add a second machine to the same gateway or move the whole thing later. A cluster is simply more than one gateway machine registered together, so if the first one goes down, the cloud service can send the next request to another member. You don't get that safety from personal mode because there's only ever one machine and one person behind it. The trade off between them comes down to who depends on the gateway. Personal mode is fast to set up, but locks everything to one person and one machine, so it turns into a problem as soon as someone else needs the same data. Standard mode asks for a proper machine that stays on, and an admin who looks after it, and in return it serves everyone and survives a machine. Going down the virtual network gateway removes the machine entirely, but it's built for data that lives in Azure rather than in your own building. If you remember one rule from this section, make it this one. The moment more than one person depends on the refresh, leave personal mode behind and move to standard only where gateways fit in Microsoft three hundred sixty five and Azure. It's tempting to file the gateway under power BI and forget about it, but that undersells what it does across the Microsoft world. That same app can serve power, automate power apps, Microsoft Fabric, and a set of Azure services, too. So one gateway can become shared plumbing for a whole organization. Inside Microsoft Fabric. The gateway does more than refresh reports. It can carry fabric data pipelines, data flow Gen two, the copy job and mirroring. And for data locked inside an Azure virtual network, the virtual network gateway is the option built for the job. The piece in the middle that hands request to your gateway is called Azure Relay, and it's the reason the gateway never needs an inbound connection. One question comes up a lot, so it's worth answering directly. A private network link into Azure like Expressroute doesn't replace the gateway. Expressroute gives you a private path between your network and Azure, and a gateway is still required to reach on premises data sources. So the two solve different parts of the same problem. There's a good reason the gateway spreads across so many services. Microsoft built it as one bridge for all of them, rather than a separate connector for each. The same outbound connection can serve a power BI refresh in the morning and a power automate flow in the afternoon. That design is why the skill carries over and why learning it once pays off well beyond dashboards. The practical takeaway is that a gateway is rarely a single reports private concern. Once it's running, other teams notice other tools, start pointing at it, and it turns into shared infrastructure that many people depend on. Treat it that way from the beginning and you save yourself a painful conversation later. The limits worth knowing before you plan a gateway is reliable, but it isn't magic, and it comes with real limits you should know before you build around it. For a start, it runs on windows only. You can't install it on a server core machine. You can't run it inside a windows container, and you can't put it on a domain controller. That means the machine you choose has to be a normal supported windows box. It also has to be a machine that stays on. The gateway only works while it's running. So a laptop that sleeps, closes, or drops off the network will break your refreshes at the worst possible time. A desktop or a server that's always awake and wired to the network is what you want, because the gateway has nothing to do if its machine isn't there. There are hard numbers worth remembering to. A gateway cluster holds up to ten members, and each gateway cluster tops out at one thousand data sources. So at real scale, you end up spreading connections across more than one gateway in direct query mode, which queries your source live instead of importing the data, power BI caps a single response at sixteen megabytes uncompressed. That ceiling shapes what you can ask for in one go. Credentials come with their own timing quirk. The gateway caches the credentials it uses for a few hours to speed up queries, and that means a password you just changed might not take effect straight away, and refreshes can keep failing until that cache clears. If you change a credential and nothing improves, wait a little before you assume something deeper is broken. Updates matter more than they look. Microsoft supports only the last six gateway releases and ships a new one every month, so a gateway left untouched for a year is running, something Microsoft no longer stands behind. Keeping it current is a small habit that prevents a whole class of failures you'd otherwise spend hours chasing. One last limit is the one people feel most when it's too late. When you install a gateway, you create a recovery key and that key is yours alone. Microsoft doesn't store it and can't retrieve it, so if you lose it, you lose the ability to recover or move the gateway, and you may end up rebuilding from scratch. Those limits only bite when they're ignored, and the ways people ignore them are predictable. The mistakes that cause most gateway trouble. Most gateway problems aren't exotic. They come from a handful of everyday mistakes, and once you know them, you can spot trouble before it reaches your reports. The most common one is putting the gateway on a laptop or a machine that gets shut down at night, because a gateway that isn't running can't refresh anything. The next one is reaching for personal mode. When a team needs the data, personal mode is tied to one user and works only with power BI. So the first time a colleague asks for the same report, everyone discovers the gateway can't be shared. Moving to standard mode fixes it, but doing that after people depend on the report is a headache. You can avoid by starting their clusters have their own traps. If you build a cluster for reliability, don't leave an offline member sitting in it. The servers can still try to send work to that dead member before it moves on, and that slows everything down. Members also need to be on the same version, because mixing versions across a cluster causes failures that are hard to explain. Sizing gets forgotten more than any of them. The gateway runs on the machine you give it. So if that machine is short on processor and memory refreshes crawl and you end up blaming the gateway for a resource problem. A machine that was fine for one report can buckle under a whole department's worth of refreshes. Then there's the recovery key, which people save badly or not at all. Losing it doesn't break the gateway today, but it takes away your safety net for the day the machine fails and you need to move the gateway somewhere new. Save it somewhere your team can find and treat it like the password it is. One more mistake hides in plain sight, and that's treating the gateway as a one time setup. Credentials expire, machines get rebuilt, and the person who installed the gateway leaves the team. So a gateway that nobody owns slowly drifts out of support, right down who looks after it, and where the recovery key lives. And the rest of the mistakes on this list get a lot less likely. How to think about gateways as you grow. Think about gateways this way as your needs grow. If you're one person refreshing your own data, personal mode can carry you for a while. The moment a second person depends on the same data, move to standard mode because sharing is the whole point of a gateway, and personal mode was never built for it. When you set up the machine, start with something reasonable rather than something minimal. Microsoft suggests beginning with at least eight processor cores, eight gigabytes of memory, and solid network connections, and then watching how much the gateway actually uses. Before you decide it's enough, put the gateway close to the data source it serves, because every hop between them adds delay to every refresh. Add a cluster when downtime would genuinely hurt and reach for the virtual network gateway when your data lives inside an Azure Virtual network. That one is managed for you and needs no machine. You also don't have to guess at any of this. The power BI service shows you the gateways you've registered, who administers them, and the connections that run through them. The whole picture sits in one place once you know where to look. None of this is complicated. Once the pieces are clear, a gateway carries data between a private network and the Microsoft cloud, and it only ever connects outward. It comes in three kinds, and most of the trouble around it comes from where you put it and how you run it. Now I want to hear from you. What is the one report in your world that you wish would just refresh on its own, and what is stopping it? If this made the topic feel less intimidating, subscribe to M365 FM to master the tools and the mindset shaping the future of work.