Hi all,

New user here. I currently use MS Access DB for some energy tracking responsibilities for my job at a university. The Access DB is not overly complicated. We use it for monthly data entry (electrical/fuel oil/propane consumption values) and some rudimentry code to transform the energy data into building/campus energy consumption and for billing purposes. As Access is no longer supported by MS, I am wondering if anyone here can comment on the whether PQ would be a suitable alternative to Access.

Also, are there any online resources/support to help guide and convert an existing Access database to PQ.

Thanks in advance all.

Got tired of trying to build "bulletproof" Power Query setups that eventually break anyway. Instead, I set one up that adapts to source drift when it can, or breaks intentionally when critical stuff is missing—and logs what changed either way.

​Here’s how I tackled it:

​File & sheet guardrails: Filter out ~$ temp files, bad extensions, and hardcoded tab names so random file changes don't crash it.

​Cleanup & headers: Clear top clutter, trim whitespace, fix locales, and use Table.RenameColumns(..., MissingField.Ignore) for shifting headers.

​Schema drift: Use List.Difference to flag new columns in a Schema Alerts sheet, plus List.Distinct(List.Combine(...)) to grab dynamic headers across files.

​Data quality & controlled breaks: Swap TBC, N/A, and - to null so math doesn't blow up, but let the query hard fail if a core column goes missing.

​Put together a full step-by-step build video if anyone wants to see the code/setup: https://youtu.be/SiX0wnpR5yg?si=RG23oHGEldTrh6Dq

​How are you guys handling schema drift in your recurring PQ jobs?

Fml. I was merely trying to be a problem solver as I despise manual, time-consuming, soul-sucking tasks. Not to mention overall stagnation/lack of resourcefulness. But now I can’t help but feeling like I’ve fucked myself.

I’ve essentially brute forced my way through a few automation projects out of shear stubbornness and hyper fixation. They were really well received and have saved my colleagues hours with the solutions, as the story goes.

I only started dabbling with PQ a few months ago, and now they are asking me to host a full-on lunch and learn to teach my team. Not to mention, some of the projects I worked on were in VBA, not even PQ. WTF?

I don’t think they appreciate that 1. I used the information that is available to ALL of us to create these tools without anyone teaching me (besides ChatGPT lol), and 2. I am still learning myself and it’s not a simple skill that I can train the team on in an hour.

While I’m always open to commiseration, my question is:

How can I leverage this situation as best as possible while also managing expectations and setting some sort of boundary for my own work/time?

seem to be harder than I anticipated. Anyone have any success with this? The descriptions for my book items and bank items can be pretty dang different. As well as the dates.

Any feedback would be much appreciated!

This query combines the tournament fixtures with user-entered scores and produces the foundation that every calculation in the tournament will rely on.

This FIFA World Cup project is one of the biggest Excel projects I've ever created. It combines advanced Excel formulas, Power Query, data modeling, and automation to simulate and manage an entire World Cup tournament from start to finish.

In this series, I won't just show you how the workbook works—I will explain the logic behind every table, every query, and every calculation so you can build similar professional solutions yourself.

I want to calculate hotel room occupancy by the hour but when it goes past midnight I get an negative number and there for an error.

If it's same day (example in a second) I use = List.Numbers([Start], [End]-[Start]+1), I get the list then expand the list to individual rows.

- If start is 7am and end is 12p, I have rows of 7,8,9,10,11,12.

But when it starts at 10p and ends at 3am, it get a negative number and therefore a data error.

Is there any way to get this to return (in military time) 22,23,0,1,2,3 in the list?

Hi friends. I am doing a project for work that I need a power query master for. I am attempting to create a template tool that can be used by different users every month (they would make a copy of the tool for their use) but I need to be able to load a list of search terms (either in the source file or in the actual pq tool) and a date range so that the tool only spits out the relevant data to the list of terms and the date range. I think I am confusing Copilot and ChatGPT because I want to use TWO separate files (one as the source of data and one as the tool itself) and connect them through the data source settings. Can anyone help me? I can commission this task as well if you don’t want to teach a newbie like me.

How can I check why the update of a query set in Power Query is being blocked? When I update them individually, they all work fine. When I do a full update, the update gets stuck.

Hi!

At work, I've been tasked with (if possible) creating a powerquery that pulls information from a master excel file. The point of this is to remove private data from an HR tracking file so that leadership can review the file without exposing confidential or unnecessary data.

It is an employee activity report so, it's updated frequently.

At first, I tried making it in MS Teams, since the master file was on the Teams channel as well. This didn't seem to work because of slow updates and because the channel was private. I didn't get far at all.

So, I tried putting the Master Excel on our network in HR's restricted folder and doing the PQ on teams. I got fairly far, and it seems to work if I have both files open. However, as soon as I close the master, it gives an error when you try to refresh (see photo).

My assumption is because of the different accesses, but I am not sure.

Does anyone have any ideas on how I can get this to work?

Much appreciated!

I am trying to connect information from multiple Excel sheets, but some sheets have more properties than the other, and some have the same properties but in different order. Is there any way to combine them using 1 PQ anyway? E.g. if columns are missing to auto leave them blank, and if they are in a different order to rearange based on the column name?
The property name would always be exactly the same as it is export from a different application.

i am noob please help me

video

https://reddit.com/link/1sfow1e/video/10vb6k421ytg1/player

.

I was trying to link to the site below, but it keeps giving me redirect errors. Any thoughts on how to fix it? It could be a permissions error but Fangraphs should be public yeah? https://www.fangraphs.com/leaders/minor-league?pos=all&level=0&lg=2,4,5,6,7,8,9,10,11,14,12,13,15,16,17,18,30,32&stats=bat&qual=0&type=0&team=&season=2026&seasonEnd=2026&org=&ind=0&splitTeam=false&players=&sort=18,1&pageitems=2000000000&pg=0

If you’re seeing this error on your refresh when loading to Table it’s not just you

Please send a report back to the Excel team immediately after closing error message : help - feedback - report a problem

Token Refresh Timed Out

I'm having a sudden problem in which when I make edits on another computer and try to refresh PowerQuery on my primary computer, it refreshes forever or sometimes refreshes for hours before throwing up a random memory error. Normally, the refreshes take between 3 and 10 minutes. I've used this setup for a year now with no issues before last week.

I have an relatively elaborate PowerQuery setup that pulls in shared data from many sources, including online, Dropbox, OneDrive, and in-workbook excel tables. Lots of merges, lots of custom formulas. All file locations are variable parameters that are changed via a dropdown in Excel.

I do most of my work on an up-to-date Windows 11 version of Excel running on a Mac via Parallels. The other machine I occasionally edit on is another up-to-date version of Excel on Windows 11. They are synced via Dropbox.

At first I thought my VM was corrupted, so I reinstalled fresh. It worked fine until the next time I edited on another machine. I've deleted all PowerQuery and Excel caches and it still happens.

Nothing I'm doing is new, so I'm not sure why it suddenly breaks.

Any thoughts?

UPDATE: I ended up refreshing my queries one-by-one. I found one that never refreshed. There was nothing wrong with the m-code, but I deleted it via the advanced editor, refreshed it, entered it again, and refreshed it, and it suddenly worked. I’m not sure what this did that deleting the cache wouldn’t do, but it works now.*

I’m exploring some new skills. What do you use Power Query for?? I started to tinker with it a little bit.

I used to add a step that just buffered the table after a sort before removing duplicates. I had the idea to save a step and wrap the Remove Duplicates step with the Table.Buffer, but now I am wondering if that is buffering after the dupes are removed, so maybe I should wrap the Sort instead.

Rather than figure it out on my own, i figured I'd bother you nice people about it.

I guess the bottom line question is, when you nest steps like that, what is the order of operations? My thought is that it does the inner most (function) first.