COURSE 3 – EXTRACT, TRANSFORM AND LOAD DATA IN POWER BI

Module 1: Data Sources in Power BI

MICROSOFT POWER BI DATA ANALYST PROFESSIONAL CERTIFICATE

Complete Coursera Study Guide

Enroll in Coursera Microsoft Power BI Data Analyst

Last updated:

TABLE OF CONTENT

INTRODUCTION – Data Sources in Power BI

In this module, you will engage in setting up data sources and delve into the various data source capabilities within Power BI. This comprehensive exploration will guide you through the process of establishing connections to different types of data sources, enabling you to understand how Power BI interacts with and utilizes these sources.

As you navigate through the module, you will gain insights into the diverse functionalities and features that Power BI offers for handling data from multiple origins, enhancing your ability to manage and analyze data effectively. This hands-on experience will equip you with the skills necessary to leverage Power BI’s full potential, ensuring you can efficiently integrate and utilize data sources to support your analytical needs and objectives.

Learning Objectives

  • Identify the different data sources and storage types in Power BI.
  • Describe how to set up a data source in Power BI.
  • Explain the different storage modes in Power BI.
  • Use data to configure storage modes in Power BI.
  • Identify the difference between structured and unstructured data.
  • Explain connectors, triggers and actions.

SELF-REVIEW: SETTING UP AN EXCEL DATA SOURCE

1. Which window should you use to select and load tables from an Excel workbook in Power BI Desktop?

  • The Navigator window
  • The Fields pane (CORRECT)
  • The Overview tab

That’s correct! The Navigator window is used to select and load tables from an Excel workbook in Power BI Desktop  

2. Which data sources can Power BI connect to?

  • Local, cloud, and SaaS sources (CORRECT)
  • Only local sources
  • Only SaaS sources

That’s correct! Power BI can connect to more than 100 source types including Local, cloud, and SaaS sources.

3. How should you optimize data ranges in an Excel workbook for better visualization in Power BI?

  • Leave them as simple worksheets.
  • Format them as tables. (CORRECT)
  • Convert them into charts.

That’s correct! If your workbook has simple worksheets with ranges of data, be sure to format those ranges as tables to get the most out of your data in Power BI. When you create reports in Power BI, the named tables and columns in the Fields pane make it much easier to visualize your data.

4. You are setting up Power BI connectors for Adventure Works. What kind of services can the company use PowerBI to connect to?

  • Power BI connects to many external apps and cloud services. (CORRECT)
  • Power BI connectors are limited to local computers or personal accounts.
  • Power BI connectors only link to Microsoft services

That’s correct! Power BI offers a range of built-in connectors that allows you to connect to various external apps and cloud services, such as Microsoft Github, Azure Blob Storage, and Dynamics 365. These connectors simplify the process of obtaining data from external sources and integrating it with your existing database, providing a consolidated view of your data across multiple platforms.

5. What should you do if you need to change the location of your source file in Power BI? 

  • Select the correct option.
  • Update the connection string. (CORRECT)
  • Create a new connection string and leave the old one unchanged.
  • Clear permissions.

Correct! Updating the connection string is necessary to ensure that your reports remain up to date and to avoid errors such as “File Not Found” or “Data Source Not Found.”

KNOWLEDGE CHECK: BASIC DATA SOURCES

1. What is the difference between a dataset and a data source in Power BI?

  • A dataset is a container that holds some of the data from a data source, whereas a data source is where the data actually comes from. (CORRECT)
  • A dataset is the same as a data source, but with fewer features.
  • A dataset and data source are the same thing.

That’s correct! This is the difference between a dataset and a data source in Power BI. A dataset is a container that holds some of the data from a data source, whereas a data source is where the data actually comes from.

2. True or False: You can create data sets that are reusable across multiple reports using basic data sources, which saves you the time and effort of having to import the same data repeatedly for each report.

  • True (CORRECT)
  • False

Correct! You only need to publish a data set into the Power BI service and it will then be available for users with the correct rights to set up and build a report based on that dataset. 

3. What are the types of workbooks that Power BI supports? Select all that apply:

  • Workbooks with ranges or tables of data. (CORRECT)
  • Workbooks with connections to external data sources. (CORRECT)
  • Workbooks with shapes and images
  • Workbooks with data models. (CORRECT)

That’s correct! It’s recommended to format simple worksheets with ranges of data as tables. This is because named tables and columns in the Fields pane make it easier to visualize your data. 

That’s correct! If your Excel workbook connects to an external data source, once your workbook is in Power BI, you can create reports and dashboards based on data from that connected source. You can also set up scheduled refreshto automatically connect to the data source and get updates.

That’s correct! A workbook can contain a data model that has one or more tables of data loaded into it by using linked tables, Power Query, Get and Transform in Excel, or Power Pivot. Power BI supports all data model properties, such as relationships, measures, hierarchies, and key progress indicators (KPIs).

4. What is the maximum size of an Excel workbook that can be uploaded to Power BI? 

  • 100 GB
  • 10 GB
  • 1 GB (CORRECT)

That’s correct! You cannot upload an Excel workbook larger than 1 GB to Power BI. 

5. True or False: When using DirectQuery in Power BI, there is a limit on the number of rows that can be returned to your underlying data source.

  • True (CORRECT)
  • False

That’s correct! Power BI imposes a limit on the query results it sends to your underlying data source when using DirectQuery. If the query returns more than one million rows, you will be shown an error and the query will fail. It’s important to keep this limitation in mind when working with large datasets.

6. Adventure Works asks you if it can change its Power BI model from Import Mode to Direct Query mode once the initial storage mode has been created. How should you respond?

Select the correct option.

  • A model cannot change from Import to Direct Query mode. (CORRECT)
  • A model can easily be switched from Import to Direct Query mode.
  • A model can only be changed within the latest version of Power BI.

That’s correct! It is possible to change a Power BI model only from DirectQuery mode to Import mode by importing all the necessary data.

7. Why would you, as a data analyst, configure Adventure Works’ storage modes in Power BI?

Select the correct option.

  • To determine how visuals access back-end data sources. (CORRECT)
  • To establish many-to-many relationships between tables.
  • To import data to Power BI.

That’s correct! In Power BI, visuals query back-end data sources based on the configured storage mode. Direct Query sends real-time queries for up-to-date information but may have slower performance, while Import mode accesses cached data for faster performance but may display outdated information if the data source has changed since the last import.

SELF-REVIEW: IMPLEMENTING TRIGGERS

1. How should your data look before loading it into Power BI?

  • Each column contains different data types.
  • The table should display columns and rows that show totals.
  • Each column contains the same data type and the table should have a header row. (CORRECT)

That is correct! Power BI accepts data that contains the same data type in each column and has a header row.

2. How can you validate that notifications of failure refreshes will be sent to the dataset owner?

  • Validating that the check box Send refresh failure notification is marked. (CORRECT)
  • Setting the correct time zone.
  • Specifying the email address of the dataset owner.

That’s correct! The check box notifies Power BI to send a notification whenever there is a failure refresh.

3. To ensure the scheduled refresh will start at 10 PM every night, you should check the __________________ window.

  • Get Data window
  • Time zone window
  • Scheduled refresh window (CORRECT)

That is correct! The scheduled refresh window will show the frequency and time of the scheduled refresh.

4. What is the benefit of understanding the difference between structured and unstructured data?

  • Understanding the difference can define the correct storage solution and the best kind of analysis needed. (CORRECT)
  • Understanding the difference can enhancing data quality and reduce data processing time.

Understanding the difference can define the correct storage solution and the best kind of analysis needed. (CORRECT)

Understanding the difference can enhancing data quality and reduce data processing time.

5. What two main factors do you need to consider when determining the appropriate data connector for Power BI?

Select the correct option.

  • The type of data source and the data’s business requirements. (CORRECT)
  • The size of the data source and the file format.
  • The level of security and the financial cost of the connector.

That’s correct! It’s important to understand the specific nature of the data source, along with where it’s stored, how often it’s updated, and its file format. You also need to understand the business requirements. Who’ll be using the data and how?

6. What is the first step to set up an online refresh schedule in Power BI services?

  • Select the Schedule Refresh button under the Refresh menu.
  • Navigate to the Datasets + Dataflows tab in your workspace. (CORRECT)
  • Modify the schedule in the Schedule Refresh pane. 

That’s correct! The first step to set up an online refresh schedule in Power BI services is to navigate to the Datasets + Dataflows tab in your workspace.

KNOWLEDGE CHECK: ADVANCED DATA SOURCES

1. What makes Azure Blob Storage an ideal option for storing unstructured data, such as photos and videos, for Adventure Works’ online retail website?

  • Azure Blob Storage is a scalable and cost-effective Cloud Storage Service, suitable for storing large amounts of unstructured data. (CORRECT)
  • Azure Blob Storage automatically converts unstructured data into structured data for easier analysis.
  • Azure Blob Storage offers structured query language (SQL) support for complex queries.

Correct! Azure Blob Storage is designed to efficiently handle and store large volumes of unstructured data, making it a suitable choice for storing photos and videos.

2. What is the purpose of the Data Source Settings option in Power BI?

  • To select the data tables to import into Power BI.
  • To change the connection details and credentials for the data source. (CORRECT)
  • To specify the location of the data source file.

That’s correct! The Data Source Settings option in Power BI allows you to change the connection details for the data source, such as the server address and database name for a SQL Server database. It also allows you to change the credentials used to connect to the data source. This option is important for maintaining the security of your data source, as the credentials are stored separately on each system and need to be set in the service separately.

3. Considering the data classification at Adventure Works for their online retail website, which type of data is most appropriately stored and queried using a SQL database? 

  • Financial business data that is structured for year-to-year comparison and is mainly read-only. (CORRECT)
  • Customer feedback in the form of audio files.
  • Product images and video files used on the website.

That’s correct! This type of financial business data, being structured and primarily used for read-only analysis such as year-to-year comparisons, is well-suited for a SQL database due to its structured nature and the efficiency of SQL in handling such data.

4. True or False: In Power BI, triggers and actions are used primarily for visualizing data, enabling data analysts to create more complex and interactive reports.

  • True
  • False (CORRECT)

Triggers and actions in Power BI are primarily used for automating workflows, like refreshing data or sending out reports at scheduled times.

5. What is the primary purpose of setting up a scheduled data refresh in Power BI for a data analyst team?

  • To automatically email the latest sales report to the management team at specified intervals.
  • To ensure that sales report datasets are regularly updated without manual intervention, improving data accuracy and workflow efficiency. (CORRECT)
  • To convert sales data from unstructured to structured formats for more straightforward analysis.

That’s correct! The main goal of establishing a scheduled data refresh in Power BI for a data analyst team is to automate the updating of datasets, which saves time and ensures that the data used in reports and dashboards is up-to-date and reliable.

MODULE QUIZ: DATA SOURCES IN POWER BI

1. Complete the following sentence: Structured data is arranged in ___________ and ___________, and can be easily analyzed.

  • columns, rows (CORRECT)
  • cells, tables
  • workbooks, tables

That’s correct! Structured data is arranged in columns and rows. By arranging data into columns and rows, it becomes easier to compare, filter, sort, and perform calculations on the data, enabling effective data analysis and decision-making.

2. Which of the following options in Power BI provides access to Power BI connectors?

  • Get Data (CORRECT)
  • Enter Data
  • Publish

That is correct! Get data option opens the menu of all connectors available in Power BI.

3. True or False: By using conditional formatting in Power BI visuals, you can utilize colors, icons, or data bars to emphasize or highlight data in either text or numeric fields.

  • True (CORRECT)
  • False

That’s correct! Conditional formatting is used for emphasizing or highlighting data by using color or icons.

4. Which file types do Microsoft Excel workbooks in Power BI support? 

  • .pdf and .jpg
  • .doc and .docx
  • .xlsx and .xlsm (CORRECT)

That’s correct! Power BI supports importing or connecting to workbooks created in Excel 2007 and later with .xlsx and .xlsm formats.

5. When you are tasked with selecting and loading tables from the Sales Data Excel workbook in Adventure Works, which window should you use in Power BI Desktop?

  • The Fields pane
  • The Navigator window (CORRECT)
  • The Overview tab

That’s correct! The Navigator window is used to select and load tables from an Excel workbook in Power BI Desktop.

6. True or False: You can set up a data source in Power BI using an Excel file with a pivot table.

  • True
  • False (CORRECT)

7. What are the steps involved in configuring a trigger in Power BI to schedule a refresh of a dataset at a specific time? Select all that apply.

  • Configure the parameters for the refresh
  • Configure the filters for the dataset
  • Choose a function to schedule a refresh (CORRECT)
  • Select the dataset to configure (CORRECT)

That’s correct! The second step to configure a trigger in Power BI is to choose a function to schedule a dataset refresh.

That’s correct! The first step to configure a trigger in Power BI is to select the dataset you want to schedule a refresh for.

8. What type of dataset requires a source data refresh in Power BI?

  • Import mode datasets (CORRECT)
  • LiveConnect mode datasets
  • DirectQuery datasets

That’s correct! Import mode datasets require a source data refresh because only this type of dataset imports data from its data sources, and the imported data might be updated on a regular or ad-hoc basis.

9. Which of the following is a limitation of data sources used in Power BI service and Power BI Desktop?

  • The total number of columns that can be used in all the tables within a dataset is restricted to 10,000 columns.
  • The maximum number of data sources allowed per user is 500.
  • Several data connectors used in Power BI Desktop need Internet Explorer 10 or a newer version for authentication. (CORRECT)

Feedback: That’s correct! Several data connectors used in Power BI Desktop need Internet Explorer 10 or a newer version for authentication, which is an important consideration for users who may need to access data from such sources.

10. What do Power BI data sources refer to?

  • The charts and dashboards used to visualize data in Power BI.
  • The different types of data that can be connected with Power BI. (CORRECT)
  • The business insights provided by Power BI reports and Q&A.

That’s correct! Data sources provide the data in different types that gets connected with Power BI.

11. Which of the following data source connectors can be found in Power BI? Select all that apply.

  • Excel Workbook (CORRECT)
  • Microsoft Word
  • SharePoint Folder (CORRECT)
  • SQL Server (CORRECT)

That’s correct! Excel Workbook is a valid data source in Power BI. You can use an Excel workbook as a data source, and Power BI can import data from Excel.

That’s correct! SharePoint Folder is a valid data source in Power BI. You can use the Power BI connector for SharePoint Folder to import data from a SharePoint folder. Paste the SharePoint site URL in the input box and select the folder you want to use.

That’s correct! SQL Server is a valid data source in Power BI. You can use the Power BI connector for SQL Server to import data from a SQL Server database.

12. True or False: When you create reports in Power BI, the named tables and columns in the Fields pane make it much easier to visualize your data.

  • True (CORRECT)
  • False

13. As a data analyst, what should you do if your Adventure Works Sales Analysis Excel workbook contains multiple sheets, with each sheet representing sales data for a specific region or time period, and has a range of data that includes columns such as date, product, quantity sold, and revenue generated?

  • Leave them as simple worksheets
  • Format them as tables (CORRECT)
  • Convert them into charts

That’s correct! If your workbook has simple worksheets with ranges of data, be sure to format those ranges as tables to get the most out of your data in Power BI. When you create reports in Power BI, the named tables and columns in the Fields pane make it much easier to visualize your data.

14. How can you ensure that your data is in a flat format with no total rows or columns when setting up an Excel data source in Power BI?

  • Convert data to pivot table format
  • Rename the Excel file
  • Format the data as a table (CORRECT)

That’s correct! When using Excel workbooks as a data source for Power BI, it is important to ensure that the data is in a flat format with no total rows or columns. Once your data is flat, it is important to format it as a table that is easy to read. This step ensures that the data is organized and ready to be imported into Power BI for analysis.

15. What is the definition of a trigger in Power BI?

  • A trigger is a function used to schedule a refresh of a dataset in Power BI. (CORRECT)
  • A trigger is a connector that links various data sources in Power BI.
  • A trigger is a Power BI report that provides insights into data trends.

That’s correct! A trigger in Power BI is used to schedule an action, such as a dataset refresh, at a specific time or when a particular event occurs.

16. Which of the following statements is true about Incremental Refresh in Power BI?

  • You can manually update the data in the dataset on a rolling basis.
  • You can refresh only the most recent data that has changed, making the refresh process faster and more efficient. (CORRECT)
  • You can refresh all the data in the dataset every time you need to update your reports.

That’s correct! Incremental Refresh is a feature in Power BI that lets you refresh only the latest data that has changed. This means you don’t have to refresh all the data every time you update your report. This makes the refresh process faster and more efficient. To achieve this, Incremental Refresh divides your data into smaller pieces, refreshing only the most recent ones.

17. True or False: The Home tab in Power BI contains quick access data source options, such as Excel, located next to the Get data button.

  • True (CORRECT)
  • False

That is correct! The quick access data source options can be found under the Home tab.

18. What is the benefit of arranging structured data in columns and rows?

  • It’s easier to analyze it (CORRECT)
  • It’s easier to delete it
  • It’s easier to publish it

That is correct! Columns and rows make data analysis easier. By arranging data into columns and rows, it becomes easier to compare, filter, sort, and perform calculations on the data, enabling effective data analysis and decision-making.

19. True or False: Power BI can connect to custom codes such as R script, Python script or providers like ODBC or OLE DB inside the Other section in the Get Data window.

  • True (CORRECT)
  • False 

That’s correct! In the Other section, there are connectors for R script, Python script, ODBC and OLE DB. 

20. What is the benefit of formatting ranges of data as tables in Power BI?

  • It improves the performance of the workbook.
  • It makes the workbook easier to share with others.
  • It makes it easier to visualize the data in Power BI reports. (CORRECT)

That’s correct! Formatting ranges of data as tables in Power BI makes it easier to visualize the data in Power BI reports. When you create reports in Power BI, the named tables and columns in the Fields pane make it much easier to visualize your data.

21. True or False: Configuring incremental refresh requires defining an incremental refresh policy only.

  • True
  • False (CORRECT)

That’s correct! Configuring incremental refresh involves creating RangeStart and RangeEnd parameters, applying filters, and defining an incremental refresh policy.

22. Structured Data can be easily analyzed and stored in __________ databases.

  • NoSQL databases
  • Azure Data Lake
  • SQL (CORRECT)

That’s correct! Structured data is indeed designed to be easily analyzed and can be stored in SQL databases. Structured data is specifically formatted and organized in a predefined manner, making it well-suited for efficient analysis and storage in SQL databases. With a clear and consistent structure, data can be easily categorized, indexed, and queried, enabling streamlined data retrieval and analysis processes. By leveraging the inherent organization and schema of structured data, businesses can leverage powerful SQL database capabilities to extract valuable insights and drive data-informed decision-making.

23. True or False: Unlike Excel workbooks stored on OneDrive or SharePoint team sites, you can’t edit local Excel files within Power BI.

  • False
  • True (CORRECT)

24. What is the purpose of the Scheduled Refresh feature in Power BI?

  • To connect directly to data sources and query for updated data.
  • To manually update the data in the dataset.
  • To automate the process of refreshing the data on a regular basis. (CORRECT)

That’s correct! The Scheduled Refresh feature is designed to automate the process of refreshing the data on a regular basis, so that users can access the most up-to-date data without needing to manually refresh it.

25. Complete the following sentence: Some data sources marked as _____ or _____ have limited functionality and are not suitable for live environments.

  • Live,  Functional
  • Legacy,  Obsolete
  • Beta, Preview (CORRECT)

That’s correct. Some data sources marked as ‘Beta’ or ‘Preview’ have limited functionality. This means that they are not suitable for live environments.

26. What is the benefit of setting up an Excel data source for Power BI?

  • To create a backup copy of the data
  • To work more efficiently with data, collaborate more effectively with others, and gain better insights into your organization’s operations. (CORRECT)
  • To export the data to another format

That’s correct! Excel is a great starting point, but Power BI offers many more powerful data analysis and visualization tools. Power BI has faster processing speed, visually appealing and customizable dashboards, powerful comparison capabilities, and a user-friendly interface. These features make Power BI a more powerful tool compared to Excel when it comes to working with data, collaborating with others, and gaining deeper insights into your organization’s operations.

27. What are the two parameters you need to set up for incremental refresh? Select the two correct options.

  • You need to set up a parameter for the size of the dataset.
  • You need to set up a parameter for the start time of the refresh window. (CORRECT)
  • You need to set up a parameter for the end time of the refresh window. (CORRECT)
  • You need to set up a parameter for the type of data source.

That’s correct! One of the two parameters needed for incremental refresh is the start time of the refresh window.

That’s correct! One of the two parameters needed for incremental refresh is the end time of the refresh window.

28. Which one of the following actions cannot be undertaken once you set the storage mode of a table to Import?

  • It cannot be modified to Upload mode.
  • It cannot be changed to Publish mode.
  • It cannot be switched to DirectQuery mode. (CORRECT)

That’s correct! If you set your storage mode to Import, it cannot be changed to any other storage mode.

29. What types of Microsoft Excel workbooks does Power BI support?

  • Workbooks created in Excel 2007 and later with a file type of .xlsx or .xlsm. (CORRECT)
  • Workbooks created in Excel 2003 and later with a file type of .xls or .xlsm.
  • Workbooks created in Excel 2010 and later with a file type of .xls or .xlsx.

That’s correct! Power BI supports importing or connecting to workbooks created in Excel 2007 and later. Some features are available only in later versions of Excel. Workbooks must be .xlsx or .xlsm file type and be smaller than 1 GB.

CONCLUSION – Data Sources in Power BI

In conclusion, this module equips you with the knowledge and skills to set up and explore various data sources within Power BI. By understanding the diverse capabilities Power BI offers, you will be able to efficiently connect, manage, and analyze data from multiple origins. This hands-on experience ensures you are well-prepared to leverage Power BI’s functionalities to support your analytical needs and objectives, ultimately enhancing your ability to make data-driven decisions and achieve successful outcomes in your projects.

Weekly Breakdown
Next Module

Quiztudy Top Courses

Popular in Coursera

Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!

Expanded Thumbnail Promo1

Mood Zone for Studying & Relaxing