Hands-On Power BI Tutorial 📊 Beginner to Pro 2023 Edition [Full Course] ⚡

Pragmatic Works・2 minutes read

Devin Knight teaches a class for beginners in Power BI, with essential resources like the Power BI Desktop application and live exercises files provided. The course covers data modeling, DAX calculations, data visualization, and sharing, emphasizing the importance of proper data modeling for accurate results.

Insights

Devin Knight is leading a Power BI class for beginners, with Manuel Quintana assisting in answering questions.
The class focuses on teaching Power BI basics and utilizes the Power BI Desktop application compatible only with Windows OS.
Live exercises with shared class files are included, and the class is recorded for later viewing on YouTube.
The Data Discovery phase involves connecting to data and cleansing it using buttons like "Get Data" and "Transform Data."
Data modeling in Power BI entails creating relationships, hierarchies, and DAX calculations for accurate report visualization.
Creating a custom date table in Power BI using DAX allows for more control and visibility compared to auto-generated date tables.

Get key ideas from YouTube videos. It’s free

Recent questions

What is Power BI Desktop?
Power BI Desktop is an application for Windows.

Related videos

Summary

00:00

"Power BI Beginner Class with Devin"

Devin Knight is the instructor for the Power BI Beginner to Pro 2023 Edition class, a refresher of a previous course due to technological changes.
Manuel Quintana, Devin's colleague, will assist in answering questions during the class.
The class is aimed at beginners in Power BI, focusing on teaching the basics.
Essential resources for the class include the Power BI Desktop application, which is only compatible with Windows operating systems.
Class files will be shared for live exercises during the session.
The class is recorded and available for later viewing on YouTube.
Power BI Desktop version can be checked by going to Help and selecting About.
The Get Data button is crucial for starting a new Power BI solution, while the Transform Data button is used for data manipulation.
The Data Discovery phase involves connecting to data and performing data cleansing using the mentioned buttons.
The Data Modeling phase includes creating relationships between different data sources to analyze complex questions.

12:54

"Efficient Data Modeling for Faster Reporting"

Data modeling involves creating relationships and building hierarchies to group fields for faster report building.
Hierarchies allow drilling into data, starting from a broader level like country down to zip code level.
Dax calculations are essential in data modeling, akin to Excel formulas, for creating additional metrics like comparing revenue.
The report view in Power BI focuses on data visualization, including building reports, drill-downs, tooltips, and custom visuals.
Proper data modeling precedes data visualization to ensure accurate results in reports.
The sharing phase in Power BI involves publishing work to the Power BI service for sharing with others and scheduling data refreshes.
Row level security in Power BI ensures users only see relevant data, a crucial aspect of data sharing.
Accessing data from data.gov for a bank's location decision involves searching for the FDIC failed Banks dataset.
Connecting to the FDIC failed Bank list file on data.gov through a web URL ensures access to the most up-to-date data.
Right-clicking on the download button for the CSV file on data.gov allows connecting to the file without downloading it.

25:34

"Connect, Cleanse, and Transform Data in Power BI"

Copy the URL of the download button from the website to use in Power BI.
Access the "Get Data" button in Power BI to connect to various data sources.
Choose the "Web" option after clicking on "Get Data" to connect to a web source.
Select the URL obtained from the data.gov website for the example.
Authenticate the connection using the anonymous connector in Power BI.
Utilize the advanced options to parameterize the URL for dynamic results.
Decide between loading data directly or transforming data for cleansing steps.
Opt for transforming data if data cleansing is required, which is often the case.
Use the Power Query Editor to perform data cleansing steps like removing unnecessary columns.
Utilize the "Choose Columns" feature in the Power Query Editor to easily search for and remove specific columns.

38:46

"Streamline list columns, edit transforms in Power BI"

To reduce the number of columns in your list, uncheck three columns and click OK at the bottom of the dialog box.
On the far right side of the screen, locate the Query Settings pane, where you can rename your query and view all data transforms applied to your dataset.
If Query Settings is not visible, access it through the View menu by selecting Query Settings.
In the Query Settings pane, you can edit or delete any transforms you've performed, allowing you to rectify mistakes like removing the wrong columns.
Utilize the Applied Steps section in the Query Settings pane to revisit and make changes to your data transformations.
To combine two columns, such as city and state, use the Column from Example feature under the Add Column tab, selecting Column from Examples and then From All Columns.
Provide an example of the desired result in the new blank column created, allowing Power BI to replicate the pattern for every row in your dataset.
Rename the new column by double-clicking on the column header and entering a new name.
Click OK to finalize the column creation, observing the M query generated by Power BI to process the data transformation.
Modify the M code by accessing the Advanced Editor under the Home tab or using the formula bar, found under the View menu, if needed.

51:46

"Analyzing Failed Banks in Power BI"

Various states were guessed to have the most failed banks, including Texas, Illinois, California, and Louisiana.
To determine which state has the most failed banks, the data needs to be loaded into the data model.
Clicking on "Close and Apply" closes the Power Query Editor and loads the data into the data model.
The data is loaded into the data model, and a table of failed banks appears in the Fields list.
To create a visual showing the state with the most failed banks, a stack bar chart is selected from the visualizations pane.
The state column is dragged into the y-axis, and the bank name column is dragged into the x-axis for a simple count.
The x-axis automatically counts the bank names, showing Georgia as the state with the most failed banks.
Data modeling is crucial in Power BI, involving creating relationships between sources, hierarchies, and DAX calculations.
Resources for learning about data modeling, including a three-hour class and a 24-minute video, are shared for further understanding.
To rename table headings or adjust visual names, navigate to the Fields pane and make the necessary changes.

01:04:27

"Creating Date Tables for Time Analysis"

Renaming the x-axis in a model can be done either within the model or temporarily within a visual by double-clicking on the axis label and renaming it.
Renaming the x-axis within a visual is specific to that visual only, while renaming it within the table affects the entire data model.
Data modeling involves having more than one table in a data model, with a date table being significant for various analyses.
A date table is crucial for analyzing specific dates, comparing holidays, weekends, weekdays, or fiscal calendars within a business intelligence solution.
The importance of a date table lies in facilitating time intelligence functions in Dax for comparing periods, year-to-date, quarter-to-date, or 12-month rolling averages.
Creating a date table can be done by importing one from a data source, using Power Query with the M formula language, or designing it with Dax in Power BI.
Designing a date table with Dax provides a learning opportunity for beginners in writing Dax and offers more control over the table's structure compared to the auto date table feature in Power BI.
While Power BI creates a date table behind the scenes, creating a custom date table allows for more visibility and customization of columns.
Third-party tools like Bravo from SQL BI can also assist in creating date tables, but creating one manually in Power BI allows for a hands-on approach.
To create a date table using Dax in Power BI, navigate to the data view on the left-hand side to access and manipulate the data columns for the table creation process.

01:17:26

Creating a Dynamic Calendar Table with Dax

Start by creating a new table in the data view by selecting Table Tools and then New Table.
Use Dax to create the new table, naming it "calendar" and using the function "calendar Auto" to generate a continuous list of dates from date columns in the data model.
The Dax formula language allows for creating calculated formulas, tables, and measures to enhance the model for report building.
The formula bar displays the object name to the left of the equal sign and its definition (Dax) to the right.
Rename the table to "calendar" and use the "calendar Auto" function to create a list of date values without breaks or missing dates.
Consider using the "calendar" function for fiscal calendars to specify start and end dates.
Add a new column to the table by selecting New Column under Table Tools to create a "year" column parsing the year from dates using the "year" function.
Create a "month number" column by selecting New Column again and using the "month" function to extract the month number from dates.
To create a "month name" column, repeat the process of adding a new column and use the "month" function to return the name of the month from dates.

01:31:13

"Creating Month Column with Format Function"

Create a final column in the table tools by selecting "New Column" for the last time.
Name the new column "month" or "month name" and use the format function to display the month name instead of the month number.
Use four lowercase M's in the format function to spell out the month name, or three lowercase M's for an abbreviation.
Explore the various options available with the format function beyond just dates by referring to the link provided.
The date table now consists of four columns: date, year, number, and month.
Take a 10-minute break after being an hour and a half into the session.
Pragmatic Works offers live boot camps for in-depth training on Power BI, DAX, and paginated reports.
Use the code "lwtn250" to get a $250 discount on any boot camp until the end of February.
Return to the report view to create a new visual using a stacked column chart to display the number of failed banks by month.
Ensure a relationship is created between the calendar table and the failed banks table in the model view to accurately display the data.

01:54:22

Understanding One-to-Many Relationships in Power BI

The relationship line between two tables indicates communication ability.
Hovering over the line highlights the columns forming the relationship.
A single arrow indicates the direction filters can occur.
Double arrows signify filters can go in either direction.
One-to-many relationship means unique values on one side and non-unique on the other.
One-to-many relationships are ideal in Power BI for data modeling.
Creating a one-to-many relationship fixes issues in the column chart.
Sorting the visual by month and in ascending order resolves sorting problems.
Displaying month names but sorting by month number is achieved through a specific property.
Calculated columns are descriptive data, while calculated measures are for numeric metrics.

02:08:56

"Creating and Using Measures in Power BI"

Measures are used to quantify and aggregate numeric values, such as sum, count, min, max, and average.
Creating measures differs slightly from creating columns or calculated columns in Power BI.
Special tricks and best practices exist for organizing measures, like creating a separate table solely for measures.
To create a measure, right-click on the table in the Fields list and select "new measure."
The formula bar displays the calculated measure, with the name on the left and the definition on the right.
An example measure is shown, named "total banks," using the Count Rows function to count rows in the "failed Banks" table.
Creating measures enhances reusability and allows for specific formatting, making it easier than using calculated columns.
Measures can be used in visuals by replacing existing calculations, providing flexibility and ease of use.
Data visualization best practices involve modifying visuals by selecting them and using the format button to change appearance.
Themes in Power BI allow for standardizing the look and feel of reports, with the ability to create, modify, and import themes using JSON files.

02:22:42

Customize Power BI themes for professional reports.

Theme creation in Power BI allows for customization and personalization.
Naming the theme internally is the first step, with no external visibility.
Colors can be freely chosen using RGB or HEX codes, with multiple options available.
Font families can be standardized, with a workaround for adding non-listed fonts.
Visuals can be enhanced by adding background colors and borders.
Borders can be customized with rounded edges using the radius property.
Themes can be saved and shared as JSON files for consistency across reports.
Exporting and importing themes is done through the View menu in Power BI.
Adding background images to reports enhances the overall design.
Titles can be added using text boxes for a professional touch to reports.

02:36:06

Enhancing Reports with Custom Visuals in Power BI

Adding aesthetic changes to a report can be done by using bold text and headers.
To insert a text box, go to "Insert" and select "Text Box."
Custom visuals in Power BI allow users to go beyond standard visuals and explore over 450 different options.
Custom visuals can be accessed through the website "AppSource" or by clicking on the three-dot menu in Power BI.
Some organizations may block custom visuals, requiring users to sign in to access them.
To add custom visuals, select "Get More Visuals" and search through the available options.
The "Play Axis" visual allows for animated reports, showing changes over time.
Custom visuals added to a report are only available in that specific Power BI file by default.
To permanently add a custom visual, right-click on it and select "Pin to Visualizations Pane."
Report Page Tooltips in Power BI allow users to create custom tooltips by adding visuals to a new page and enabling the "Allow use as tooltip" option in the format section.

02:49:42

Activating City-State Mapping in Power BI

City-state column needs to be activated for mapping in Power BI.
Data categories feature needs to be turned on to recognize city-state as a geographic field.
Select city-state column, go to column tools, change Data category to "place" to enable mapping.
After setting Data category to "place," data points will be mapped on the report visual.
Add state column to the tooltip section to filter the tooltip based on the state being hovered over.
State column in the tooltip allows for a more interactive and informative tooltip display.
Use the insert button Navigator under the insert menu to create navigation buttons for report pages.
Buttons created through Navigator allow users to navigate between different report pages easily.
Publish the report to the Power BI service by selecting the publish button under the Home tab.
Share the published report with others by selecting a workspace and accessing it through the Power BI web browser experience.

Try it yourself — It’s free.