course_dv

PowerBI Software

This covers material from the following Microsoft Learn modules:

• Data Analytics
• Get Started with Power BI
• Get data in PowerBI
• Clean, transform, and load data

Download data files

• Data Shaping
• College Earnings
• Titanic Data

Installation

Install the desktop version PowerBI on your pc:

• Download the latest version of PowerBI
• Log in with your mail.wvu.edu account, and not your mix.wvu.edu email. The former is a Microsoft Account, and the latter is a Google account.
• Click on the down arrow on the top-right corner of the screen. Choose Power BI Desktop. You should be taken to the Microsoft Store.

Data Analytics

Identify levels:

• Descriptive: what happened?
• Diagnostics: why did it happen?
• Predictive: What will happen?
• Prescriptive: What should happen?
• Cognitive: (newish) Machine learning algorithms

Identify roles:

• Business analyst: closer to business
• Data analyst: closer to data
• Data Engineer: Manage data pipeline
• Data Scientist: Statistics / data mining
• Database administrator: Tech person in charge of storing data

Tasks of a Business analyst:

• Prepare data: majority of time
• Model: create relationships
• Visualize: create charts
• Analyze: statistics, charts, etc…
• Manage: security, distribution, updates, etc…

Get Started with Power BI

Software:

• PowerBI Desktop: desktop app, Windows-only
• PowerBI Service: online platform used with a browser
  • Workspace is a shared online space
    • Contains an app, a simplified interface to access reports and dashboards
    • Contains data and rules for controlling access
• PowerBI Mobile: app for iPhone/Android

Building blocks:

• Semantic model is a model of data sources, data tables, and transformations
• Visualization / Report
• Dashboard
  • Made of tiles

Get Data in Power BI

• Database
  • Choose authentication mode
  • Select tables to import
  • Write SQL query
    • Know that you can use a custom parameter in a query
• File
  • Locations: Local file, OneDrive, SharePoint
  • Know how to refresh or change a datasource by clicking on Data source settings
• Non-relational database
  • Describe how you you can pick a non-relational database as a source.
• Storage mode
  • Import: load all data into a local copy
  • DirectQuery: keep data in database, ideal for large datasets
  • Dual: Some data is imported, others kept in database
• Errors
  • Understand how to relink a file to import

Clean, transform, and load data

Understand the role of PowerQuery Editor, and how it is different from PowerBI

Features:

• Use First Row as Headers
• Rename columns
• Remove top/bottom/null rows and columns
• Pivot / Unpivot data
• Rename table
• Replace values (or null values)
• Remove duplicates
• Datatypes
  • Change datatype
  • Understand purpose for decimal, fixed decimal, whole, percentage, date/time, text, and boolean (TRUE/FALSE)
• Tables
  • Append queries: used for identical columns
  • Merge (or join): use to do a left/right outer or inner join
• View table statistics to profile your data