Connecting to Advanced Data Sets in Power BI

Name: Connecting to Advanced Data Sets in Power BI
Author: Elizabeth Vergara

Learn how to connect to different types of data sets in Power BI

Preview the first lesson free — get full access to all 5 lessons.

Course: On-Demand

In this video, we will review connecting to nested XML data source. Let's get started. XML stands for extensible markup language. It is a structure for storing and transporting data. XML documents are formed as element trees. An XML tree starts at a root element and branches from the root to child elements. Parents have children, children have parents and siblings are children on the same level or brothers and sisters. All elements can have text, content, and attributes. If we open our XML file, we'll see that we have customers and orders within the root parent element. If we expand that nesting, we can see within the customer's parent, we have attributes of the customer, including a nesting of customer address. If we expand the orders element, we can see all the attributes of the order as well as the nesting of ship information. Now that we have examined the data let's use Power BI to connect and import this data. So we'll navigate back to Power BI. On the home ribbon, we'll select Get Data, select More, select File, click on XML, and then click Connect. We'll navigate to our XML file and click Open. Here we can see that Power BI has detected two tables, customer and orders. If we select each table we're showing a preview of that data. Let's click on Transform Data. When we review the table we see that the customer's table contains a nesting under full address. We can flatten this nesting by clicking on the expand arrow icon within the address column which will show us the various attributes that we can flatten. Here we have the option to use the original column name as a prefix, which we'll do and we'll click Ok. Notice how the nested attributes have now become columns within our table. We'll now navigate to the orders table and we'll see on the ship info column that we have a nesting. Here we'll click on the expand arrow icon within the ship column which will show us the various attributes we can flatten here. Again, we have the option to keep ship info as our prefix for these columns. We'll click Ok. And notice how the nested attributes have now become flattened columns within our table after we have expanded that particular nesting. Finally, we'll click on Close and Apply. We'll navigate to the modeling tab where we can now create a relationship between these two tables. We'll click on the customer table and the order table and we'll make the customer ID the key, one-to-many single filtering relationship. We'll click Close. And now we're able to begin analyzing our data. In the field's pane we've got our customer and our order data with the flattened XML that we just imported into our Power BI file. This concludes our session on connecting to XML data. Thank you for watching.

Intermediate Provider : Elizabeth Vergara 5 Lessons · 17m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

Power BI can revolutionize the way you analyze and present data. With its intuitive interface and robust features, it is a powerful business intelligence tool that gives you the ability to transform raw data into meaningful insights and captivating visualizations. Being able to pull from multiple data sets gives you the ability to create a unique data set that is easier to visualize and analyze. This can unlock new ways to refine and optimize different functions, systems, and processes within your organization.

In this course, you will learn how to connect to advanced data sources using Power BI Desktop. You will learn how to flatten nested XML and JSON data sources, and we will explain how to connect to a live Power BI Service data set and R and Python scripted data sets. By the end of this course, you will be able to utilize many different data sets to gain further insight into optimizing your organization.

Learning Objectives:

What You'll Learn

Connect to XML data sources and flatten nested XML using Power BI Desktop
Connect to JSON data sources and flatten nested JSON data
Connect to a live Power BI Service data set
Connect to R scripted data sets in Power BI
Connect to Python scripted data sets in Power BI
Utilize multiple advanced data sets to gain further insight into optimizing your organization

Key Takeaways

Power BI is a business intelligence tool that transforms raw data into meaningful insights and visualizations through its intuitive interface and robust features.
Pulling from multiple data sets lets you create a unique data set that is easier to visualize and analyze.
Power BI Desktop can connect to advanced data sources, including nested XML and JSON that can be flattened.
The course covers connecting to a live Power BI Service data set as well as R and Python scripted data sets.
Combining many different data sets can unlock new ways to refine and optimize functions, systems, and processes within an organization.

Frequently Asked Questions

What will I learn in this course?

You will learn how to connect to advanced data sources using Power BI Desktop, including how to flatten nested XML and JSON data sources, connect to a live Power BI Service data set, and connect to R and Python scripted data sets.

What data sources does this course cover?

It covers connecting to XML, JSON, live Power BI data sets, R scripts, and Python scripts.

What tool is used in this course?

The course uses Power BI Desktop to connect to advanced data sources.

What will I be able to do by the end of the course?

By the end of the course, you will be able to utilize many different data sets to gain further insight into optimizing your organization.

Why is connecting to multiple data sets useful?

Pulling from multiple data sets lets you create a unique data set that is easier to visualize and analyze, which can unlock new ways to refine and optimize different functions, systems, and processes within your organization.