Data sources for the Power BI service - Power BI (2023)

  • Article
  • 9 minutes to read

Data is at the heart of Power BI. You can explore data by creating charts and dashboards, or by asking questions with Q&A. The visualizations and answers get their underlying data from a dataset, which comes from a data source.

This article covers the data source types you can connect to from the Power BI service. There are many other types of data sources. To use these other data sources in the Power BI service, you might need to use Power BI Desktop or Excel's advanced data query and modeling features first. For more information, see Other data sources.

To get data from Power BI data sources:

  1. On your Power BI site, select Get data in the bottom-left corner of the page.

    Data sources for the Power BI service - Power BI (1)

  2. Under Discover content or Create new content, choose how you want to get data.

    (Video) Configure data sources for Power BI Reports in Power BI Report Server

    Data sources for the Power BI service - Power BI (2)

Discover content

The Discover content section offers data and reports that are already prepared for you. Select My organization to discover apps published by other people in your organization, or select Services to choose apps from online services that you use.

Many services have template apps for Power BI. Most services require you to have an account. For more information, see Connect to services you use with Power BI.

Create new content

The Create new content section has options for you to create or import content. Power BI has two ways to create or import your own content, Files and Databases.

Files

  • Excel workbooks, or .xlsx and .xlsm files, can include different data types. For example, workbooks can include data you enter into worksheets yourself, or data that you query and load from external data sources by using Power Query. Power Query is available through Get & Transform Data on the Data tab of Excel, or through Get External Data in Power Pivot. You can import data from tables in worksheets, or import data from a data model. For more information, see Get data from files for Power BI.

  • Power BI Desktop or .pbix report files query and load data from external data sources to create reports. In Power BI Desktop, you can extend your data model with measures and relationships, and publish the .pbix files to the Power BI service. Power BI Desktop is for advanced users who have a good understanding of their data sources, data query and transformation, and data modeling. For more information, see Connect to data in Power BI Desktop.

  • Comma-separated value or .csv files are simple text files with rows of data that contain values separated by commas. For example, a .csv file that contains name and address data might have many rows, each with values for first name, last name, street address, city, and state. You can't import data into a .csv file, but many applications, like Excel, can save simple table data as .csv files.

  • For other file types, like XML (.xml) or text (.txt), you can use Excel Get & Transform Data or Power BI Desktop Get data to query, transform, and load the data first. You can then import the Excel or Power BI Desktop file into the Power BI service.

    (Video) How to set up a DATA GATEWAY to connect your reports to local data sources in Power BI

Where you store your files makes a significant difference. OneDrive provides the greatest flexibility and integration with Power BI. You can also keep your files on your local drive, but if you need to refresh the data, there are a few extra steps. For more information, see Get data from files for Power BI.

Databases

From the Power BI service, you can connect live to the following databases in the cloud:

  • Azure SQL Database
  • Azure Synapse Analytics, formerly SQL Data Warehouse
  • Spark on Azure HDInsight

Connections from Power BI to these databases are live. For example, you can connect to an Azure SQL Database and begin to explore its data by creating reports in Power BI. Whenever you slice data or add a field to a visualization, Power BI queries the database directly. For more information, see Azure and Power BI.

For other database types, you need to use Power BI Desktop or Excel to connect to, query, and load data into a data model. You can then import that file into Power BI where a dataset exists. If you configure scheduled refresh, Power BI uses the configuration and connection information from the file to connect directly to the data source. Power BI queries for updates and loads the updates into the dataset. For more information, see Connect to data in Power BI Desktop.

Other data sources

You can use hundreds of different data sources with Power BI. The data must be in a format consumable by the Power BI service. Power BI can then use the data to create reports and dashboards and answer questions with Q&A.

Some data sources already have data formatted for the Power BI service. These sources are similar to template apps from service providers like Google Analytics and Twilio. SQL Server Analysis Services tabular model databases are also ready to use.

In other cases, you might have to query and load the data you want into a file. For example, your organization might store logistics data in a data warehouse database on a server. But the Power BI service can connect to that database and explore its data only if it's a tabular model database. You can use Power BI Desktop or Excel to query and load the logistics data into a tabular data model that you then save as a file. You can import that file into Power BI where a dataset exists.

If the logistics data in the database changes every day, you can refresh the Power BI dataset. When you import the data into the dataset, you also import the connection information from Power BI Desktop or the Excel file.

(Video) How to Replace Data Source in Microsoft Power BI| How to Change or Update Data Source in Power BI

If you configure a scheduled refresh or do a manual refresh on the dataset, Power BI uses the connection information with other settings to connect directly to the database. Power BI then queries for updates and loads those updates into the dataset. You probably need a Power BI gateway to secure any data transfer between an on-premises server and Power BI. When the transfer is complete, visualizations in reports and dashboards refresh automatically.

So even if you can't connect to your data source directly from the Power BI service, you can still get your data into Power BI. It just takes a few more steps and maybe some help from your IT department. For more information, see Data sources in Power BI Desktop.

Datasets and data sources

Power BI frequently uses the terms dataset and data source, and often uses them synonymously. But datasets and data sources are two different things, although they're related.

Power BI creates a dataset automatically when you use Get data to connect to and import data from a template app, file, or live data source. A dataset contains information about the data source and data source credentials. The dataset also often includes a subset of data copied from the data source. When you create visualizations in reports and dashboards, you often look at data from the dataset.

The data in a dataset comes from a data source. For example, data could come from the following data sources:

  • An online service like Google Analytics or QuickBooks
  • A database in the cloud like Azure SQL Database
  • A database or file on a local computer or a server in your organization

Data refresh

If you save your file on a local drive or a drive in your organization, you might need a Power BI gateway to be able to refresh the dataset in Power BI. The computer that stores the file must be running at refresh time. You can also reimport your file, or use Publish from Excel or Power BI Desktop, but those options aren't automated processes.

If you save your files on OneDrive for work or school or SharePoint - Team Sites, your dataset, reports, and dashboard are always up-to-date. Because both OneDrive and Power BI are in the cloud, Power BI can connect directly to your files or import the files into Power BI. Power BI connects about once every hour and checks for updates. The dataset and any visualizations refresh automatically if there are any updates.

Template apps from services also automatically update, once a day in most cases. You can manually refresh, but whether you see updated data depends on the service provider. Updates to template apps from people in your organization depend on the data sources they use, and how the app creator configured the refresh.

(Video) How to refresh web sources in Power BI service

Azure SQL Database, Azure Synapse Analytics (formerly SQL Data Warehouse), and Spark on Azure HDInsight are cloud data sources. The Power BI service is also in the cloud, so Power BI can connect to those data sources live by using DirectQuery. With DirectQuery, Power BI is always in sync, and you don't need to set up a scheduled refresh.

SQL Server Analysis Services is a live connection to Power BI just like an Azure cloud database. The difference is that the database is on a server in your organization. This type of connection requires a Power BI gateway, which an IT department can configure.

Data refresh is an important part of Power BI. For a thorough understanding of data refresh, see Data refresh in Power BI.

Considerations and limitations

Data sources for the Power BI service have the following limitations. Other limitations apply to specific features, but the following list applies to the full Power BI service:

  • Dataset size limit. Datasets stored in shared capacities in the Power BI service have a 1-GB size limit. For larger datasets, use Power BI Premium.

  • Distinct values in a column. When a Power BI dataset caches data in Import mode, it can store a limit of 1,999,999,997 distinct values in a column.

  • Row limit. When you use DirectQuery, Power BI imposes a limit on the query results that it sends to your underlying data source. If the query sent to the data source returns more than one million rows, you see an error and the query fails. The underlying data can still contain more than one million rows. You're unlikely to reach this limit, because most reports aggregate the data into smaller sets of results.

  • Column limit. The maximum number of columns allowed across all tables in a dataset is 16,000 columns. This limit applies to the Power BI service and to datasets Power BI Desktop uses. Power BI uses this limit to track the number of both columns and tables in the dataset, which means the maximum number of columns is 16,000 minus one for each table in the dataset.

    (Video) Power Bi Data Source Connection: How to change Data source setting in Power bi

  • Data source user limit. The maximum number of data sources allowed per user is 1000. This limit applies to the Power BI service only.

Next steps

  • Connect to services you use with Power BI
  • Get data from files for Power BI
  • Data refresh in Power BI
  • DirectQuery in Power BI
  • What is an on-premises data gateway?
  • Data sources in Power BI Desktop

FAQs

Which data source types you can connect to from the Power BI service? ›

File data sources
  • Excel Workbook.
  • Text/CSV.
  • XML.
  • JSON.
  • Folder.
  • PDF.
  • Parquet.
  • SharePoint folder.
Dec 13, 2022

Which of the following data sources does Power BI Q&A support in Power BI service? ›

Q&A now supports SQL DirectQuery sources, including SQL Server 2019, Azure SQL Database, and Azure Synapse Analytics. You can use Q&A to ask natural-language questions against these data sources.

How many data sources are available in Power BI? ›

Hevo Data with its strong integration with 100+ Data Sources (including 40+ Free Sources) such as Power BI allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready.

What is the limitation for dataset in Power BI service? ›

Dataset limits

There's a 1-GB limit per dataset that is imported into Power BI, unless the workspace is in a Power BI Premium capacity. See the Large datasets. However, if you choose to keep the Excel experience, instead of importing the data, the limit is 250 MB for the dataset.

What are examples of data sources? ›

Primary Data Sources
  • Autobiographies and memoirs.
  • Diaries, personal letters, and correspondence.
  • Interviews, surveys, and fieldwork.
  • Internet communications on email, blogs, listservs, and newsgroups.
  • Photographs, drawings, and posters.
  • Works of art and literature.
6 days ago

Which type of connection supports more data sources? ›

Apparently, Power BI supports more than 160 of data source types. Connection Type is the way that the connection is created to the data source. One data source can support multiple types of connections. For example, you can connect to SQL Server database with Import Data or DirectQuery.

What are the three 3 different types of data source? ›

Types of data sources
  • relational.
  • multidimensional (OLAP)
  • dimensionally modeled relational.

What are the 2 types of sources of data? ›

There are two types of data sources: machine data sources and file data sources. Although both contain similar information about the source of the data, they differ in the way this information is stored. Because of these differences, they are used in somewhat different manners.

How many types of data sources are there? ›

Data can be gathered from two places: internal and external sources.

What are common data limitations? ›

Some examples of limitations include a limited sample size or lack of reliable data such as self-reported data, missing data, and deficiencies in data measurements (such as a questionnaire item not asked that could have been used to address a specific issue).

What could be a limitation of a dataset? ›

This is determined by the number of fields added to the dataset, and is a complex interaction between the number of each of the different types of data. 1024 is the absolute maximum of integer fields allowed. Maximum column count: 1024 columns per table.

What are the 3 most important sources of data? ›

Sources of Data
  • Observation Method.
  • Survey Method.
  • Experimental Method.

What are the 5 sources of data? ›

The Top 5 Sources of Data on your Website
  • Reports: Conversion funnel and pathing. Study these reports. ...
  • Internal search queries. This seems like a no-brainer, but some brands forget to assess what people are searching for on their website. ...
  • Store locators. ...
  • Reviews & Customer Service inquiries. ...
  • Domain reports.

What is data source answer? ›

A data source is simply the source of the data. It can be a file, a particular database on a DBMS, or even a live data feed. The data might be located on the same computer as the program, or on another computer somewhere on a network.

What are the best sources of data? ›

Discover the best free big data sources below:
  • Amazon Web Services. Amazon Web Services provides a registry of open data to help users discover and share datasets. ...
  • Chartr. ...
  • Data Portals. ...
  • Google Scholar. ...
  • Open Data Network. ...
  • Pew Research Center. ...
  • Statista. ...
  • CIA World Factbook.
Aug 20, 2022

What are the data sources you will connect? ›

Let's take a closer look at the different data sources we support.
  • 1) Databases. Relational databases are a common data source that you can easily connect to datapine. ...
  • 2) Flat Files. ...
  • 3) Social Media. ...
  • 4) APIs and other Platforms. ...
  • 5) database access. ...
  • 6) Join Data Sources.

What are data sources for dashboard? ›

Dashboards contain many data-driven elements such as maps, charts, lists, and gauges. The data sources used to power these elements come from web map layers, web layers stored in your organization, or from feature sets generated with data expressions.

What are the 6 sources of data? ›

The most commonly used methods are: published literature sources, surveys (email and mail), interviews (telephone, face-to-face or focus group), observations, documents and records, and experiments.

What are the 4 types of sources? ›

Types of Sources
  • Scholarly publications (Journals) A scholarly publication contains articles written by experts in a particular field. ...
  • Popular sources (News and Magazines) ...
  • Professional/Trade sources. ...
  • Books / Book Chapters. ...
  • Conference proceedings. ...
  • Government Documents. ...
  • Theses & Dissertations.
May 5, 2022

How do you identify data sources? ›

Determining Data Sources and Ownership
  1. Identify organizations that provide information. Locate all the organizations that manage information essential to your enterprise. ...
  2. Identify tools and processes that are information sources. ...
  3. Determine how centralizing each piece of data affects the management of data.

What is the primary source of data? ›

A primary data source is an original data source, that is, one in which the data are collected firsthand by the researcher for a specific research purpose or project.

What are the 4 methods of data collection? ›

The main techniques for gathering data are observation, interviews, questionnaires, schedules, and surveys.

What are the main sources of data collection? ›

Some examples of sources for the collection of primary data are observations, surveys, experiments, personal interviews, questionnaires, etc.

What are the 3 most common data types? ›

Most programming languages support basic data types of integer numbers (of varying sizes), floating-point numbers (which approximate real numbers), characters and Booleans.

What are the 4 types of data examples? ›

4 Types of Data: Nominal, Ordinal, Discrete, Continuous | upGrad blog.

What are the 4 common big data challenges? ›

Because of the constantly evolving data sources and the increasing amounts of generated data, companies face severe problems in achieving high-quality data integration. Those challenges altogether can also be called "The 4 V's of Big Data". They are data Veracity, Volume, Variety, and Velocity.

What are the three types of data collection errors? ›

Sample frame error. Selection error. Non-responsive error. Measurement (observational) error.

Which is best tool for data analysis? ›

The 9 Best Data Analytics Tools for Data Analysts in 2023
  • Microsoft Excel.
  • Python.
  • R.
  • Jupyter Notebook.
  • Apache Spark.
  • SAS.
  • Microsoft Power BI.
  • Tableau.
Nov 30, 2022

What are the main errors while collecting data? ›

An introduction to measuring sampling error and the effects of non-sampling error is provided in the following sections.
  • Sampling Error. ...
  • Factors Affecting Sampling Error. ...
  • Standard Error. ...
  • Variance. ...
  • Relative Standard Error. ...
  • Confidence Interval. ...
  • NON-SAMPLING ERROR.

What happens if a dataset is too small? ›

Models trained on a small dataset are more likely to see patterns that do not exist, which results in high variance and very high error on a test set. These are the common signs of overfitting. So, your main goal when working with small datasets is to avoid overfitting.

How many sources of big data are there? ›

A significant part of big data is generated from three primary resources: Machine data. Social data, and. Transactional data.

How many data sources are in a data studio? ›

Google Data Studio allows you to blend up to 5 data sources in one chart.

What are 5 sources of data? ›

Methods of Collecting Primary Data
  • Direct personal investigation.
  • Indirect oral investigation.
  • Information through correspondents.
  • Telephonic interview.
  • Mailed questionnaire.
  • The questionnaire filled by enumerators.
Jun 29, 2020

How do you manage data from multiple sources? ›

5 ways to manage multiple data sources for high-performance apps
  1. Know what data to combine. ...
  2. Use data visualization. ...
  3. Turn to data blending tools. ...
  4. Create virtual database services through abstraction. ...
  5. Decide where to host data sources.
Feb 28, 2019

How do you get data from multiple sources? ›

How to Integrate Data from Multiple Sources
  1. Step 1: Decide Which Sources to Use. The first step is to identify which data you want to integrate. ...
  2. Step 2: Choose a Data Integration Method. ...
  3. Step 3: Estimate the Size of the Extraction. ...
  4. Step 4: Connect to the Data Sources.
Apr 14, 2022

Videos

1. Power BI Tutorial: 4 Ways To Get Data
(Guy in a Cube)
2. Publish to the Power BI Service and Update a Published Report
(Daxel)
3. Making data source parameters easy in Power BI Desktop
(Guy in a Cube)
4. Making sure your Power BI web data source refreshes online (RelativePath, Query, BrowserContents)
(BI Elite)
5. 10.8 How to refresh reports Automatically in Power BI Service | Power BI Tutorial for Beginners
(Pavan Lalwani)
6. What is Power BI Service? How to use Power BI Service? | Power BI Service Tutorial | Power BI | 4K
(BI Consulting Pro)
Top Articles
Latest Posts
Article information

Author: Neely Ledner

Last Updated: 11/05/2022

Views: 6321

Rating: 4.1 / 5 (62 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Neely Ledner

Birthday: 1998-06-09

Address: 443 Barrows Terrace, New Jodyberg, CO 57462-5329

Phone: +2433516856029

Job: Central Legal Facilitator

Hobby: Backpacking, Jogging, Magic, Driving, Macrame, Embroidery, Foraging

Introduction: My name is Neely Ledner, I am a bright, determined, beautiful, adventurous, adventurous, spotless, calm person who loves writing and wants to share my knowledge and understanding with you.