sluofoss

Recent Notes

1. get data in power bi

Jan 12, 20256 min read

data from file

Flat file location

The first step is to determine which file location you want to use to export and store your data.

Your Excel files might exist in one of the following locations:

  • Local - The file isn’t moved into Power BI, and a link doesn’t remain to it. Instead, a new semantic model is created in Power BI, and data from the Excel file is loaded into it. Accordingly, changes to the original Excel file aren’t reflected in your Power BI semantic model. You can use local data import for data that doesn’t change.

  • OneDrive for Business If any changes are found, your semantic model, reports, and dashboards are automatically updated in Power BI.

  • OneDrive - Personal you’ll need to sign in with your personal OneDrive account, and select the Keep me signed in option. Check with your system administrator to determine whether this type of connection is allowed in your organization.

  • SharePoint - Team Sites

Change the source file

Power Query provides many ways for you to accomplish this task, so that you can make this type of change when needed.

  1. Data source settings
  2. Query settings
  3. Advanced Editor

data from relational source

sql server login credential options

  • Windows - Use your Windows account (Azure Active Directory credentials).
  • Database - Use your database credentials. For instance, SQL Server has its own sign-in and authentication system that is sometimes used. If the database administrator gave you a unique sign-in to the database, you might need to enter those credentials on the Database tab.
  • Microsoft account - Use your Microsoft account credentials. This option is often used for Azure services.

avoid writing sql in power bi and have it in a view instead. power query will do query folding on view by default (optimized) can also for custom queries, but cumbersome to set up.

data source setting change source edit permissions clear permissions

scenario Create dynamic reports for multiple values

TIP

can use invoke custom function

steps:

  1. create a parametrized sql query in pbi
  2. create another query that loads multiple value.
  3. for the parametrized sql query, create function and disable load
  4. for the value query (2), add column → invoke function

data from nosql

in power query after importing json, row should show as records. in the table header in power query, there should be an expand button that flattens the json.

data from online source

get data → online service category

storage mode

The three different types of storage modes you can choose from:

  • Import
  • DirectQuery (realtime, resolve latency issue)
  • Dual (Composite)

dual have same limitation as DQ

  • if data changes, you must refresh to show the latest data.
  • If data changes, there’s no guarantee of consistency between visuals.
  • You must refresh in Power BI Desktop to reflect schema changes.
  • A limit of 1 million rows can return on any query.
  • You can’t change a model from import to DirectQuery mode.

data from azure analysis service

similar to getting data from SQL Server, in that you can:

  • Authenticate to the server.
  • Pick the model you want to use.
  • Select which tables you need.

Notable differences between Azure Analysis Services and SQL Server are:

  • Analysis Services models have calculations already created.
  • If you don’t need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX).

Connect live as the underlying model is compatible with power bi semantic model, it keeps calculations all in the same place.

fix performance issues

Optimize performance in Power Query

Power Query takes advantage of good performance at the data source through a technique called Query Folding.

Query folding

Query folding is the process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you’re actively making transformations.

The benefits to query folding include:

  • More efficiency in data refreshes and incremental refreshes. transform within source rather than local in power bi
  • Automatic compatibility with DirectQuery and Dual storage modes.

Native queries aren’t possible for the following transformations:

  • Adding an index column
  • Merging and appending columns of different tables with two different sources
  • Changing the data type of a column

Query diagnostics

home ribbon ⇒ tools ⇒ session diagnostics ⇒ start diagnostics

Other techniques to optimize performance

Other ways to optimize query performance in Power BI include:

  • Process as much data as possible in the original data source.

  • Use native SQL queries. When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you aren’t pulling data from stored procedures or common table expressions (CTEs).

  • Separate date and time, if bound together. If any of your tables have columns that combine date and time, make sure that you separate them into distinct columns before importing them into Power BI. This approach will increase compression abilities.

resolve data import errors

Query timeout expired

hese timeouts can be configured for any timespan, from as little as five seconds to as much as 30 minutes or more.

Power BI Query Error: Timeout expired

This error indicates that you’ve pulled too much data according to your organization’s policies. Administrators incorporate this policy to avoid slowing down a different application or suite of applications that might also be using that database.

You can resolve this error by pulling fewer columns or rows from a single table.

If you need the rows, columns, and complexity, consider taking small chunks of data and then bringing them back together by using Power Query. For instance, you can combine half the columns in one query and the other half in a different query. Power Query can merge those two queries back together after you’re finished.

We couldn’t find any data formatted as a table

(import from excel error) Power BI expects to find data formatted as a table from Excel.

Couldn’t find file

change file path in data source

Data type errors

Sometimes, when you import data into Power BI, the columns appear blank. This situation happens because of an error in interpreting the data type in Power BI. The resolution to this error is unique to the data source. For instance, if you’re importing data from SQL Server and see blank columns, you could try to convert to the correct data type in the query.

Instead of using this query:

SELECT CustomerPostalCode FROM Sales.Customers

Use this query:

SELECT CAST(CustomerPostalCode as varchar(10)) FROM Sales.Customers

Graph View

Backlinks