Connecting to Common Power BI Data Sources
A sales analyst has four possible starting points for a new Power BI report. Monthly results arrive in a file. Detailed orders sit in a database. Forecast figures come from a cloud service.
The organization also publishes a shared semantic model containing approved sales measures and business definitions. All four options might expose useful data, and Power BI might allow a connection to each one. What evidence would show that the analyst chose the right connection, rather than one that merely opened without an error?
That question reaches the heart of the PL-300 skill in this episode: identify and connect to data sources or a shared semantic model. The important words are identify and connect. You’re not simply looking for a connector whose name resembles the source. You’re identifying what the business needs, what kind of source can provide it, and whether an existing semantic model already provides an authoritative version of the information.
Begin with the requested outcome. Suppose the sales director wants a report that compares actual revenue with forecast revenue by region. Before choosing a connector, the analyst needs to know where those values come from, what shape they have, and whether the organization has already defined revenue and region in a shared model. A monthly spreadsheet may contain revenue totals, but that doesn’t automatically make it the preferred source.
The database may hold more detailed transactions. The cloud service may own the forecasts. The shared semantic model may already combine governed information and expose an approved revenue calculation.
Those alternatives don’t behave identically after connection. Different connectors can expose different authentication, navigation, query folding, and refresh behavior. Authentication concerns the identity and credential context used to reach the source. Navigation concerns what the connector makes available for selection, such as the relevant data objects exposed by that source.
Refresh behavior affects how the Power BI solution obtains current data after the initial connection. Query folding concerns whether supported Power Query work can be handled through the source rather than only after data has been retrieved. The exact behavior depends on the connector and source, so familiarity with one connection doesn’t prove that another will behave the same way.
Power Query is Power BI’s environment for acquiring and transforming data. Its formula language is called M. If the analyst connects to raw source data and needs to select, clean, combine, or reshape it, that work belongs in the query and preparation part of the solution. It happens for the purpose of producing the query output that will feed the model. A semantic model has a different responsibility.
It organizes the analytical data and business logic used by reports. DAX calculations operate in that model context, while report interactions determine what a consumer sees when making selections. Access and refresh states can also depend on the Power BI service. Choosing a connection therefore affects more than the first successful preview.
Now consider the authoritative shared semantic model. If it already satisfies the sales director’s requirement, connecting to it can avoid recreating governed business logic. The analyst doesn’t need to rebuild a local interpretation of revenue merely because the original file and database are available.
That would create another place where definitions could diverge. The useful question isn’t, “Can I connect to the raw data?” It’s, “Do I need raw data to produce this required result, or has an authoritative model already done the necessary preparation and modeling?”
The opposite decision can also be valid. If the shared model doesn’t contain the forecast figures required for the comparison, its existence alone doesn’t satisfy the request. The analyst must verify the model’s actual data shape and available business content.
Perhaps the forecasts must come from the cloud service and require preparation before they can support the report. The right answer follows the requirement. It isn’t a universal preference for either raw sources or shared models.
Imagine that another analyst connects directly to the monthly file because it’s familiar and immediately available. The file opens, the expected column names appear, and a basic report produces totals. That’s configuration success, but it isn’t yet evidence of solution correctness. The file could contain only a monthly snapshot while the requested analysis needs transaction detail.
Its regional values might not align with the governed regional definitions. It might duplicate calculations already maintained in the shared model. Even when the numbers look plausible, the analyst still has to verify source type, credential context, data shape, and whether the authoritative shared model already satisfies the requirement.
Data shape is especially important because the same subject can be represented at different levels of detail. A file with one row per region and month can support some summaries, while detailed orders can answer different questions. A successful connection can’t create missing detail.
If the director later asks to investigate individual orders, the monthly regional totals won’t suddenly provide those records. Conversely, choosing detailed source data when the approved shared model already supplies the required analysis can introduce unnecessary duplication. The business question determines what shape is sufficient.
The layer that owns a problem determines where you should investigate it. Suppose the analyst expects five fields from a cloud source, but the Power Query output contains only four. Changing a report visual won’t restore the missing field.
The symptom appears in query output, so the source selection, navigation, and query work deserve attention. If the query output is correct but a DAX result changes unexpectedly under a filter, the investigation belongs in model behavior and calculation context rather than in the original connector. If the report is correct for its author but a consumer can’t access it, inspect the access state where consumption occurs instead of repeatedly changing transformations.
This distinction prevents random troubleshooting. Start by reproducing the observed failure. Record what should happen and what actually happens. Then identify the layer that directly governs that behavior.
You might inspect query output for missing or malformed data, the semantic model for analytical behavior, a DAX result for calculation behavior, the report for interaction behavior, or the service for refresh and access state. A green confirmation after entering connection information proves only that the connection step succeeded. It doesn’t prove that the complete business requirement works.
Return to the sales report. Assume the normal regional view looks correct, but the result becomes incomplete when the analyst selects a particular reporting period. The first task is to reproduce that condition consistently. Which period is selected?
Which values are expected? Which values disappear? Without that controlled comparison, it’s easy to change an unrelated setting and mistake a different result for a repair.
Next, inspect the evidence in order. If the relevant period never appears in Power Query output, the failure precedes the semantic model and report. The analyst should examine the source, the selected data, and the query steps that produce that output. If the records appear in query output but not in the analytical result, attention shifts to the semantic model or DAX behavior.
If the result works during authoring but fails after a service refresh or for another consumer, refresh or access state becomes relevant. The point isn’t to check every possible layer every time. It’s to start where the symptom says the behavior is owned.
After correcting the cause, the analyst retests both the failing period and the original normal case. Testing only the edge condition could hide a new side effect. Testing only the normal case would fail to prove that the reported defect was fixed. Verifiable evidence might include the corrected query output, the expected result under both filter states, a successful refresh state, or the intended consumer experience. The appropriate evidence depends on where the correction occurred.
This is why connecting deliberately involves more than obtaining data once. A file connection, database connection, or cloud-service connection can feed Power Query, but each connector can present its own way of authenticating, navigating available data, supporting folding, and refreshing. Those differences influence how the solution behaves after development. The analyst should be able to explain which source was selected, what identity context reaches it, what data shape arrives, and how continued access to current data will be assessed.
A shared semantic model changes the decision because it may already contain governed business logic. If the organization has established an authoritative model for sales reporting, rebuilding the same definitions from a database extract isn’t automatically more flexible or more correct. It may simply produce a second interpretation.
Before creating that duplicate, compare the requirement with what the shared model actually supplies. If the required dimensions, values, and logic are present, using that model preserves the existing analytical foundation. If something essential is absent, identify the gap rather than assuming the shared model can answer a question it wasn’t built to answer.
Notice how the nearest alternative can change several consequences at once. Connecting to a raw database may give the analyst source-level data that still needs query preparation and semantic-model design. Connecting to an authoritative shared semantic model can reuse work that already exists.
Connecting to a file may satisfy a bounded requirement when that file has the correct content and lifecycle, but its convenience doesn’t establish authority. A cloud service may be the system that owns a required forecast, yet its connector behavior still has to be considered. There’s no single setting that makes one category universally correct.
The same requirement-driven reasoning protects data quality. Suppose revenue is stored as expected, but region names differ between the forecast source and the sales data. A connection to both sources can succeed while their outputs remain unsuitable for the intended comparison.
Successful access establishes that Power BI reached the sources. It doesn’t establish that the resulting shapes align or that the report’s business meaning is correct. The analyst needs evidence from the query output and downstream result, not confidence based on the absence of connection errors.
It also protects calculation behavior. If a report uses an existing semantic model, the analyst should evaluate the results that model provides under relevant filters. If the analyst instead prepares raw data and builds a model, then query output, model behavior, and DAX results all become part of the validation chain. A total that appears correct without filters might not prove that the data and logic behave correctly by region or period. The condition named in the requirement must appear in the test.
Report usability is another downstream consequence, but it shouldn’t be confused with data acquisition. A visual can be formatted perfectly while being driven by incomplete query output. A report interaction can expose an unexpected result caused by model behavior.
Correcting the visual alone won’t repair an upstream data problem. At the same time, correct source data doesn’t guarantee that every report interaction presents the intended consumer result. Validation has to occur in the layer where each behavior becomes observable.
Refresh and access follow the same principle. An analyst may connect successfully during authoring using one credential context, then encounter a different refresh or consumer state later. That doesn’t mean the original connector was necessarily the wrong connector, nor does it prove that the report design caused the failure. It means the investigation should identify the relevant credential context, refresh state, or access state instead of treating the initial authoring session as complete proof. Detailed credential and privacy decisions deserve their own treatment, but here the essential habit is to record which context worked and which one didn’t.
Consider a final comparison. Analyst A chooses the familiar file, sees rows, builds a chart, and declares the work complete. Analyst B restates the requirement, confirms that actual revenue exists in an authoritative shared semantic model, verifies that the forecast must come from the cloud service, checks the shapes needed for comparison, and identifies how each result will be validated. Analyst B may still encounter a problem. The difference is that the second approach produces an explainable architecture and a path to isolate the problem.
If the forecast is missing after refresh, Analyst B can ask whether it’s absent from query output or merely absent from the report result. If actual revenue disagrees with an expected governed value, the analyst can confirm whether the report really uses the authoritative model or a duplicated local definition. If a consumer can’t see the completed report, the analyst can distinguish consumer access from source transformation. Each observation narrows the investigation because the requirement has already been mapped to the layer responsible for it.
For an exam-style scenario, resist the urge to select an option simply because its interface label contains the same noun as the question. Identify the requested outcome first. Then ask what kind of source contains the required data, whether its shape is sufficient, which credential context applies, and whether a shared semantic model already supplies the authoritative answer. Consider connector behavior where it matters, including navigation, folding, and refresh. Finally, name the evidence that would prove the result in the relevant layer.
That evidence should be specific. “ The connection worked” is weak because it says nothing about completeness or downstream behavior. “ The required records appear in Power Query output for both tested periods” is stronger evidence for a query-level requirement. “ The governed measure returns the expected result under the required region and period filters” addresses model and DAX behavior. “
The refresh state is successful and the intended consumer receives the expected report experience” addresses later operational behavior. You don’t need every type of evidence for every decision. You need evidence matched to the behavior you claim is correct.
The practical standard is simple but demanding. Restate the requirement without naming a favorite feature. Identify the source or authoritative shared model that can satisfy it. Determine which Power BI layer owns the behavior.
Connect using the appropriate source context, then inspect the resulting data shape and downstream result. When something differs from expectations, reproduce it, correct the controlling cause, and retest the normal and failing conditions. That process turns a connection from a convenient configuration into a supported analytical decision.
Choosing a Power BI connection starts with the required business result, not with the most familiar connector. Files, databases, and cloud services can differ in authentication, navigation, folding, and refresh behavior. An authoritative shared semantic model may also satisfy the requirement without duplicating governed business logic. Once you’ve chosen, prove the outcome where it actually occurs.
Inspect query output for acquisition and transformation results, model or DAX behavior for analytical results, and refresh, access, or consumer state for service behavior. A successful connection is useful evidence, but it’s only one piece. The stronger decision is one you can connect to the requirement, place in the correct Power BI layer, and verify under both the expected condition and the condition most likely to expose a failure.
Compare connecting to a file, a database, a cloud service, and a shared semantic model. What constraint would cause you to reject one source path even when the connection succeeds? The companion episode reference organizes the details you should inspect while reproducing the lesson in Power BI. This lesson was produced by Bare Metal Cyber dot com.