How to use Power BI with Excel

[@kennethstephani692]

Another awesome video, Christine!!

[@kebincui]

Great video. Thanks Christine 👍

[@bryankendig3989]

Control T is a great way to create a table within excel

[@bi-ome]

That's a good tip!

[@mirrrvelll5164]

Nice to know. I think it's useful as always. You've touched on some interesting topics, such as premium, data flows, and using DFs as a source. It would be valuable to get some insight (from your perspective) on these topics, such as Premium and Fabric. Is this essential for (mid-sized) companies, like the topic on data flows (best advice, where to do ETL steps)? What I see as one of the best is using data from semantic models (direct query). Somehow I am not satisfied with how people explain this or documents about it. I use it daily, and I see a lot of stuff that nobody warns about, like RLS in different places, permissions in different stages, view as the user doesn't work when using direct query...well, you name it. Lots of stuff when you dig a little deeper.

[@bi-ome]

That's a big and interesting question! IMO dataflows only shine for centralizing cloud sources (like Excel, GSheets, Smartsheet, or SP lists), or in places where IT is really stingy with SQL access. It's more of a stopgap I'd say. The direct query on model bits feels similar to me - if you have a model and you really wish you could add one more table, but it's not appropriate to include it in the base model, it makes sense, but the direct query part is I think where a lot of us get hung up. The "thin reports" concept IS good, where you really just reference the same model in a lot of reports. That goes somewhat hand-in-hand with the "golden model" concept, where I see it touted as the best practice - having a single source of truth for all reports to draw on. I have issues with it in practice, mostly because that golden model gets to be the bottleneck and you always need to bring in an extra table or two on any given project. It gets so massive and complex that everyone's hesitant to touch it to make updates. It's always one particular person's brainchild, too, and if that person leaves you're in hot water for a bit while everyone else wraps their brain around it. I like the concept of having the source of truth golden model, but I don't think everyone *has* to use that one golden model in an org. If you have documented some known queries against that golden model that should always yield the same numbers, and the business logic that goes into how things are measured is documented, it makes it a lot easier for whole teams to get comfortable with the "right" way to come up with the "right" numbers. That way when people make their own small spinoffs, they have something to check against, and there's less of a contingency chain. I like it a bit more decentralized if that makes sense? But not too decentralized? 🤣 My ideal strategy is to cultivate an environment where BI devs feel comfortable making models from scratch if they need to, because the business logic is out there and available and there's safety in being able to know you have it "right" to an extent by doing a number check. Fabric... I can see where things are going, and I like it, but I don't think it's quite there yet. It's not essential YET but it's essential to be learning it IMO. It has value, but it's like any other MS product where it's kind of rocky the first few years. There's a huge opportunity for awesomeness with Fabric the way it's positioned - e.g. I'm really interested to see where the Purview integration goes, options for AI/ML in the pipelines, obvy direct lake is a big draw.

[@mirrrvelll5164]

@@bi-ome@bi-ome Thanks for your answer, and I appreciate your taking the time to answer this. I cant agree more with you about this. =)

[@NezzyLawd]

Thanks for the video! As a side note, is there any way to bypass the refresh limit when refreshing your semantic model? For example, If Excel was the data source and was being updated hourly and it was driving Visuals on a PowerBI report?

[@bi-ome]

It will always be based on your license tier, so 8 on pro or 48 per day if you have premium. Using things like Power Automate to run the refresh won’t bypass that. BUT if you instead have a flow that sends your Excel data to SQL, you can use direct query on it and it’ll “always” be up to date-

[@NezzyLawd]

@ Great recommendation, I’ll try that out!

[@mienzillaz]

I know that I'm not the target audience for this clip, but still I think that helper queries are a "no no" regardless of your M skills. One of the biggest fails delivered by MS. Creates unnecessary items which only confuse people. Sure it creates function, sure it invokes that in following steps, but as soon something isn't right debugging it is too much for casuals.

[@bi-ome]

Maybe, but there’s not really a no-code alternative that I’m aware of to combine files in a folder, particularly for people who don’t have a SQL server. Debugging anything at all is a learned skill, we get better at it through practice. I wouldn’t call people “casuals” - we are all just people trying to complete a given task with the tools that are available to us. That one person has more background outside of analytics than inside it doesn’t make them casual, they have just had a different focus. :) I have been accused of being non-technical in my life, only to turn out to be the opposite given some time to figure out the particular area I hadn’t spent a lot of time in before, so this is a heated topic for me!

[@mienzillaz]

@bi-ome by casual I mean in given setup/tool. If you don't use PQ to often in your daily work you're casual.. and if you find the merit (I know it's not an easy task with way I write things) in what I wrote up there, I'm just saying that this method is really confusing. I know this cause I was in that spot myself, and yes: I was casual in M too.