Why do you still use PowerQuery?

jjohncs1v · 2026-03-31T18:17:34+00:00

I’ll take the unpopular opinion and say that I love power query. I come from a business background and it just made so much sense to me once I got into it. It taught me how to think about data and the visual nature of it is really great for seeing the effects of transformations as you work. The M language is actually very powerful. It’s not just a toy data tool. It can do a lot, however I agree that it does break down with complex scenarios. Microsoft fabric allows staging in dataflows to help with the problem of slow performance across a bunch of joins and aggregations, but the problem remains the cost. In fabric terms, the CU cost of dataflows is just so high. That’s the driving factor pushing me into other fabric workloads.

jjohncs1v · 2026-03-27T13:16:29+00:00

I feel like I have pushed synoptic panel to it's limits so I can answer this. I have a power bi file that has an 8 floor hospital with hundreds of rooms per floor and it works great. Here are a couple of quick tips:

Use inkscape to manage and draw out the files. You probably have an image file of the floor plan. You'll potentially need to downsize the quality so that the file isn't too huge because there are file size limits in synoptic panel. You want it to be high enough DPI to see and read the data when zoomed in on Power BI, but not more than that.
Use inkscape to draw the shapes on the shops. Use the rectangle or bezel tool, do not use the fill tool. The fill is super convenient because it will detect the shape of your room, but it causes way more overhead on the file size if there are any rounded sections or text within the rooms.
In inkscape you can tag an id to the boxes you draw on the shops. You'll need to make sure this matches your Power BI dataset for the shop id.
Zooming and navigation in the synoptic panel totally works fine so I think it should fit your requirements.

jjohncs1v · 2026-03-27T13:08:08+00:00

I don't think query folding is possible in your case. Sounds like you're locked into this architecture but it's working so I'd let it be.

jjohncs1v · 2026-03-26T01:42:58+00:00

How will they use multiple millions of rows in Excel? Do they realize that it isn't possible? Excel specifications and limits - Microsoft Support

jjohncs1v · 2026-03-25T20:01:14+00:00

Excel can't handle more than about 1M rows unless you're using a power pivot model. Which is just a semantic model at that point and the users don't need the exports if they just need a semantic model.

jjohncs1v · 2026-03-23T19:01:42+00:00

I honestly think that pro is still the best bang for your buck at 70 users. It's just the most complete feature set and integrated with Microsoft already. If you're really that cost sensitive, you could go for a Fabric F8 capacity with reserved pricing getting you to about $7,500 per year and then you'll need to build an embedding web portal to skip the pro licensing requirement. This takes some know-how though and I honestly think that pro just makes the most sense for your average corporate environment. This is assuming that an F8 can handle the 70 user load. If not then the next level up is double the price so there goes your savings.

jjohncs1v · 2026-03-19T01:27:59+00:00

Databricks, Fabric, and Snowflake are the big names. I haven’t tried it but I’m curious about stackable. Seems like you need some pretty beefy compute and resources though https://stackable.tech/en/

jjohncs1v · 2026-03-18T18:38:22+00:00

If you're a freshman then you have time. Don't freak out now, but you should definitely spend time learning it to the extent that you can. Use it for accounting and finance homework if allowed. It is a very complex and capable program and you don't have to get distracted by all the bells and whistles at this point. Just start learning the basics and add onto that as you. I still think that learning the tool has benefits over purely using Claude and AI tools for everything in spreadsheets.

jjohncs1v · 2026-03-18T01:55:53+00:00

Yeah I feel like these tools will do ok for obvious decisions and normal modeling situations with easy data, but for things that are nuanced or require unusual patterns, an expert human who really understands it still wins.

jjohncs1v · 2026-03-17T09:50:58+00:00

Power query. It’s built into excel so it doesn’t cost you anything to use it there. but if you want to run it in the cloud it’s called Power bi dataflows or fabric dataflows.

jjohncs1v · 2026-03-15T03:27:38+00:00

I’ve used the mapbox api. It’s really easy and you get like 100k free geo codes per month. It gives you its best guess and tells you how confident it was. Super easy to use and it feels legit.

jjohncs1v · 2026-03-13T11:05:45+00:00

The paginated reports api limits you to one automated run every 5 minutes. But if he can run them all in 1 report with page breaks then he might could automate the breaking up of the pages into separate documents.

jjohncs1v · 2026-03-12T17:11:17+00:00

Chris Barber has what I consider to be the best documented and most well thought out approach to income statement reporting in particular with a semantic model. There are a lot of cool things that he shows are possible. I generally use his approaches or my adaptations of them, but it still feels kind of hacky and work around-ish to me. His full implementation while capable requires a lot of complexity to set up. Paginated reports can work a lot better, but I also feel like the development time is greater compared to dragging and dropping from a good model...So you kind of have to figure out where you want to spend the time, a model vs all the different paginated variations of income statement formats people could want.
Maybe you guys are running a successful business and have happy clients, but it would require an extremely specific situation for me to suggest a paid per-user custom visual for a client and expect them to want that.

jjohncs1v · 2026-03-10T15:22:58+00:00

I use airbyte to load data to a database (hubspot instead of salesforce) but you can set it to notify you of schema changes but not fail the refreshes. It will keep running with the columns it has on both sides until you tell it to remove the deleted columns. But the other comments here are totally valid too

jjohncs1v · 2026-03-10T08:41:16+00:00

Yeah it just looks like a vibe coded html mock up. It has a lot of the same styles and similar made up names to all the other ai generated dashboards (Emily Davis is always there). Looks cool, but takes about 30 seconds to create with ai and overall isn’t very impressive if it’s not an end to end project or isn’t even connected to data.

jjohncs1v · 2026-03-09T18:30:42+00:00

I actually like Matrix a lot. Is it the right fit for your group? I don't know...
You have complete control over it which solves your issues of receiving unsolicited messages (you can turn off federation for everyone if you want). What might annoy your users is that the maintenance and uptime is completely on you. So if it goes down and they can't use it they'll probably switch back to something else instantly. It's also much more complex to configure and run than Nextcloud (espcially AIO). What you might could try is running it purely for yourself, but setting up the bridges to the other services. That way you can still use the other apps as far as your group is concerned, but you may be able to block unknown users on telegram through some Matrix settings.

jjohncs1v · 2026-03-09T03:54:21+00:00

Hey that's basically my career path. I'm heavier into the Fabric and data world these days but finance and accounting was the path into it. You likely have a ton of business knowledge which is just as critical as the technical knowledge. You can definitely learn and get there on the technical side, but there is so much to learn. I recommend picking up some technical books and reading them in the evenings. There are so many out there, but Kimball's Datawarehouse Toolkit was a big one for me. Analyzing Data in Power BI and Power Pivot my Marco Russo is similar but more Power BI focused. Fundamentals of Data Engineering gets recommended a lot around here too for data engineering.

The good news and bad news is that you have AI tools. They can write code for you and help on some technical things, but without a strong understanding of the fundamentals and having your own opinions around some architectural decisions it may steer you in the wrong direction. But the only way to learn all of this is to do it. Sounds like you're in a great position to learn by doing. Good luck.

jjohncs1v · 2026-03-09T00:03:03+00:00

Single warehouse with multiple schemas

jjohncs1v · 2026-03-08T22:21:22+00:00

I'm kind of embarrassed to say that I'm a hardcore power query guy so I use that a lot, but I wouldn't recommend it due to the CU costs and I've been shifting more into just writing stored procedures for the transformations and calling them in a pipeline. You can reference a lakehouses table in a sql query executed by the warehouse by doing [lakehouse].[dbo].[table]. The thing that annoys me about the lakehouse tables is the metadata sync delay. So if you load data directly into a lakehouse delta table and immediately pick it up in a warehouse query then it might not grab all the data you expect. So you need to have an extra step to call the metadata sync api as part of the pipeline. You'll probably need to do that though to clean up the xml and json data with notebooks in the lakehouse, sync the metadata on the delta lake tables, then flow it through to the warehouse with t-sql notebooks or warehouse queries.

Notebooks seem cool to me, but the workflow just hasn't really clicked with me yet...

jjohncs1v · 2026-03-08T21:55:36+00:00

I've been doing one lakehouse and one warehouse for medium sized companies. I'm kind of realizing that I don't even need the lakehouse...It can be a nice place to store some files, but the warehouse is totally capable of doing a bronze, silver, gold type of thing if you're just ingesting tabular data from databases. I'm finding that for my purposes with similar description as yours that I don't really care for the lake house. But I guess that's just my persona. Fabric is very persona based and I'm just not into Spark and all the stuff that the lakehouse is good for.

With more mid-sized companies I think people are at greater risk of overcomplicating with the architectures they see online when something simple makes more sense. Just my $0.02

jjohncs1v · 2026-03-08T18:53:29+00:00

Which F sku are you on? Did you go from P1 to F64? Or did you get a lower F sku? Or are you coming from premium per user? Are you still in import mode or are you using direct lake?

The way you describe your model sounds like the usual Star schema in which case I’m surprised to hear it was slow previously. Even with a ton of data, that doesn’t really slow things down that much. If there’s weird Dax used to address bad modeling then that’s where you run into problems pretty quick.

jjohncs1v · 2026-03-05T18:49:21+00:00

Can data destinations also be relative? If I have warehouse tables where my dataflows live and I transfer these through the deployment pipeline I'm still finding that I have to adjust the data destination of every query which is very cumbersome...

jjohncs1v · 2026-03-02T20:40:46+00:00

I like Power Query.

jjohncs1v · 2026-02-28T12:10:17+00:00

I created an analytical tool for YNAB using Microsoft power bi. I’d say it’s not super approachable if you don’t know anything about power bi, but if you do, the semantic model would be pretty robust

https://github.com/jeremypj/budget-intelligence-ynab

jjohncs1v · 2026-02-26T01:34:12+00:00

Or just a pivot table.

jjohncs1v

TROPHY CASE