Before we dive into the configuration process, let’s first comprehend the significance of data loading in Power BI. When you connect to a data source in Power BI Desktop, you have the option to load data directly into your data model or edit the data before loading. Loading the data imports all the data from the selected tables into your data model, whereas the edit option enables you to refine the data using Power Query Editor before importing it into your data model.
How to Load Data in Power BI
After connecting to a data source, you’ll come across two options: Load and Edit. If you wish to import the data without making any changes, click on ‘Load’. The ‘Edit’ option triggers Power Query Editor, where you can manipulate data before loading.
Configuring Data Loading using Power Query
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. Power Query makes it easy to extract data from various sources, clean and transform the data, and then load it into Power BI.
In the Power Query window, you can apply a variety of transformations to your data including:
- Removing unnecessary columns
- Changing the data type of columns
- Filtering rows
- Creating calculated columns
- And many more
After applying the necessary transformations to your data, you can then load it into your report by clicking on the ‘Close & Apply’ option in the Home tab.
For example, if you want to filter out any rows where the ‘Sales’ column has a value less than 100,000, you can do so using Power Query:
- Select the ‘Sales’ column
- Go to ‘Number Filters’
- Select ‘Greater Than’
- Enter 100000
- Click ‘OK’
Then, the rows where ‘Sales’ is less than 100000 will be removed from your dataset.
Loading Modes in Power BI
In Power BI desktop, there are two modes of data loading:
- Import Mode: In this mode, data is imported into Power BI’s in-memory engine. The advantage of using this mode is that it provides quick and responsive reports. However, it does have a limit of 1GB of data per dataset.
- DirectQuery Mode: This mode allows you to build visualizations over “live” data. In other words, each time a report is rendered, Power BI Desktop queries the external database directly, ensuring your data is always up-to-date.
The choice of loading mode depends on several factors such as data size, data source, and updating needs.
By mastering data loading configurations, you’ll be one step closer to passing the PL-300 Microsoft Power BI Data Analyst exam. Practice loading different types of data from various sources and play around with the many transformation options in Power Query to become proficient in this vital step of the Power BI data analysis process.
Practice Test
True or False: Direct Query and Import are two data connectivity modes in Power BI.
- True
- False
Answer: True
Explanation: Power BI supports multiple data connectivity modes including Direct Query and Import.
In Power BI, when using Direct Query, you can load large volumes of data.
- True
- False
Answer: False
Explanation: When using Direct Query, the data is not loaded into Power BI, rather the queries are sent directly to the data source.
Power BI Report Server allows you to configure data loading for queries.
- True
- False
Answer: True
Explanation: Power BI Report Server is a solution that customers deploy behind their firewall to manage reports and prepare for data loading.
Multiple select: What actions can you perform to configure data loading for queries in Power BI?
- A) Data shaping
- B) Partitioning
- C) Data transformation
- D) Creating data gateways
Answer: A. Data shaping, B. Partitioning, C. Data transformation
Explanation: Data shaping, partitioning, and transformations are all essential components of configuring data loading for Power BI queries.
The Import data connectivity mode in Power BI brings all the data into Power BI and allows you to use Power BI to create queries.
- True
- False
Answer: True
Explanation: Import is a data connectivity mode within Power BI that loads the data from the data source into the Power BI model.
Single select: What does ‘scheduled refresh’ in Power BI refer to?
- A. Scheduling report updates
- B. Scheduling data refreshes
- C. Scheduling query updates
- D. None of the above
Answer: B. Scheduling data refreshes
Explanation: Scheduled refresh in Power BI refers to the process of updating the data in your reports at a regular interval.
You can increase the performance of your Power BI report by reducing the amount of data loaded.
- True
- False
Answer: True
Explanation: Limiting the data that is loaded into Power BI can help increase the report’s performance by reducing workload and storage demands.
Single select: When using Direct Query in Power BI, what measure is appropriate to limit data?
- A. Row-level security
- B. Column-level security
- C. User-level security
- D. None of the above
Answer: A. Row-level security
Explanation: Row-level security (RLS) can be used with Direct Query in Power BI to limit data access at row level.
True or False: In order to refresh data in Power BI, you need to have a Power BI gateway installed and configured.
- True
- False
Answer: True
Explanation: Power BI uses a gateway to connect to your data source and refresh the data.
Single select: What impacts the performance of Power BI reports?
- A. Size of the data source
- B. Complexity of calculations
- C. Both A and B
- D. None of the above
Answer: C. Both A and B
Explanation: Both the size of the data source and the complexity of calculations can impact the performance of Power BI reports.
Data gateways in Power BI are required only when dealing with on-premises data sources.
- True
- False
Answer: True
Explanation: Data gateways in Power BI are used to sync on-premises and cloud data to provide updated data for reports.
Multiple select: Which of the following are types of data gateways in Power BI?
- A. Personal mode
- B. Enterprise mode
- C. Private mode
- D. Public mode
Answer: A. Personal mode, B. Enterprise mode
Explanation: Power BI provides two types of data gateways, the personal mode and the enterprise mode.
Direct Query mode in Power BI does not support data transformation within Power BI.
- True
- False
Answer: True
Explanation: In Direct Query mode, the transformations are done in the source system as the data isn’t loaded into Power BI.
Single Select: Which one is not a part of data loading configurations in Power BI?
- A. Data shaping
- B. Calculations
- C. Managing parameters
- D. Building visuals
Answer: D. Building visuals
Explanation: Building visuals is a part of report generation process, not data loading configurations.
To use Direct Query Mode, the data source must support the SQL language.
- True
- False
Answer: True
Explanation: The data source must support SQL to enable query folding which is an essential part of the Direct Query Mode.
Interview Questions
What is the purpose of data loading configuration in Power BI?
Data loading configuration in Power BI is used to define how data will be imported into Power BI and how it’ll refresh. It impacts performance, resource utilization, and data accessibility.
How can you modify the data loading settings in Power BI?
Data loading settings can be modified from the Options dialog under “Data Load” menu. It allows to specify how Power BI behaves while loading data.
What implications does the ‘Enable load’ option in Power BI have on queries?
The ‘Enable load’ option allows a query to contribute its data to the overall data model. If ‘Enable load’ is off, Power BI will not load data from that table into the overall model, which affects the availability of that data for reporting and analysis.
What does the ‘Include in report refresh’ option in Power BI do?
The ‘Include in report refresh’ option ensures that the data from the selected queries are automatically refreshed whenever the report is refreshed. If this is not checked, the data will remain static and will not update even if new data is available.
Where can you configure the refresh settings of your dataset in Power BI?
The refresh settings of the dataset can be configured in Power BI service under “Settings” > “Datasets”.
What are the two primary methods of data refresh in Power BI?
The two primary methods of data refresh in Power BI are scheduled refresh, where data reloading occurs at defined intervals, and on-demand refresh, which is initiated manually by the user.
What is Query Folding in Power BI and why is it important?
Query folding is a technique where Power BI pushes data transformations back to the source database instead of performing the operation in Power BI. It is important as it reduces the computational workload on Power BI and can improve data loading performance.
Can you use DirectQuery and live connections in Power BI simultaneously?
No, DirectQuery and live connections are mutually exclusive in Power BI. A report can’t combine connection types.
What is Incremental Refresh in Power BI?
Incremental Refresh is a feature where data is refreshed in intelligent, incremental chunks, loading only data that has changed since the last refresh instead of reloading the entire dataset.
What’s the difference between Import mode and DirectQuery regarding data loading?
In Import mode, data is imported into Power BI’s memory and reports interact with this in-memory data for queries. In DirectQuery mode, no data is imported. Instead, queries are sent directly to the underlying data source.
How can you check whether Query Folding is taking place or not in Power BI?
You can use the Query Dependencies view in Power Query or the View Native Query option. If the View Native Query is greyed out, it indicates that Query Folding isn’t taking place.
Can Power BI use sources with different privacy levels in a single query?
Yes, Power BI supports combining sources with different privacy levels in a single query, but it might lead to certain queries not being able to fold.
What is ‘Load more’ in Power BI?
‘Load more’ is a feature in Power BI Desktop that allows loading more values in the column inside the data view, beyond the default limitation of Power BI.
What is the significance of ‘Detect relationships after load’ option in Power BI?
‘Detect relationships after load’ option automatically finds and creates relationships between tables based on their columns after the data is loaded. It can save a lot of time in complex data models.
Why might one choose to disable the ‘Auto date/time’ option in Power BI data load settings?
Disabling ‘Auto date/time’ can improve data load performance, avoid unnecessary date tables, and give the user more control over the data model. However, it removes the automatic generation of Year, Quarter, Month, and Day hierarchies.
extutorials.com