Introduction
Power BI - Is a complete reporting solution that offers data preparation, data visualization, distribution and management through development tools and an online platform.
There are Three Major Components of Power BI
Power BI Desktop(desktop application application) - For creating semantic models and reports with visualization.
Power BI Service(online platform) - Dashboards from published reports and distribute content with aps
Power BI Mobile(cross-platform mobile app) - For on-the-go access to the PBI service content, designed for mobile.
Analytics Mindset
- Analytic mindset encompasses using data to identify trends, ask relevant questions to inform decision making.
- Analytical mindset is more evidence -based rather than using assumptions or intuition.
Analytics Mindset Helps:
- Asks why something is happening, not just what happened.
- Breaks down problems into measurable components.
- Looks for trends, patterns, and outliers in data.
- Uses data to make informed decisions and recommendations.
The Flow of Power BI is :
- Connect to data with Power BI Desktop
- Transform data with Power Query Editor
- Model data with Power BI Desktop
- Create Visualization and reports with Power BI Desktop
- Publish Reports to Power BI Service
- Distribute and Manage reports in Power BI service.
Building Blocks of Power BI
- Semantic models - consist of all the connected data, transformation, relationships and calculations.
- Visualization- when you create a visualization , you add it to the canvas for a report page.
1. Get Data In Power BI
Steps :
Open Power BI Desktop
Connect to different data sources under Get Data
Preview source data with Power Query
(i)Flat File- We can get data from different flat files and then as well be able to change the data source.
- Open Power BI click on Get Data then under common data sources choose Excel Workbook or Text/csv.
(ii) Relational data- We can as well get data from relational data sources
- Get data -> Click on from SQL Server(enter servers and database).
- Data Connectivity mode > Import
- Advanced Options to type your SQL Query
- Change data source settings under Transform data Power BI uses a view, when it retrieves data, it participates in query folding , a feature of Power Query.
(iii) NoSQL database - You can get data from NoSQL database like Azure, Azure Cosmos db, data stored in JSON format.
(iv) Online Service - We can get data from online services eg SharePoint, OneDrive, Dynamic 365, Google Analytics then enter the URL.
(v) Azure Analysis Service - A fully managed PaaS. Its a similar approach as getting data from sql server.
- authenticate to the server
- pick the model you want to use
- select
Power BI Storage Model
We have three modes of storage:
Import
Direct Query
Dual(composite)
2. Transform data with Power Query Editor
- Power Query Editor helps with data cleaning and profiling.
- The
valueortablein a column represents that the table has relationship with other tables and can be used to join tables. - At Power Query Editor helps profile data in PBI.
Profiling data is about studying the naunces of the data : determining anomalies, examining and developing the underlying data structures and querying data statistics eg row counts, value distribution, min and max values, average.
under View -> Data Preview
-> Check Column Quality
-> Column Profile
-> Column Distribution
-> Value Distribution
-> Column Statistics
- When the Distinct = Unique it means the column contains unique values and this helps create 1-1 relationship.
- Power Query Editor helps us with data cleaning, transformation and then load, that is,
(i) Unpivot column, pivot columns and transpose columns.
(ii) Resolve inconsistencies, unexpected or null values, data quality issues.
(iii) Apply user-friendly value replacement.
(iv) Profile data so you can learn more about a specific column before using it.
(v) Evaluate and transform column data types.
(vi) Apply data shape transformations to table structures.
(vii) Combine queries.
(viii) Apply user-friendly naming convention to columns and queries.
(ix) Edit M code in the Advanced Editor.
The Various Power BI Functions and their Categorization
- DAX functions (Modeling & calculations)
Used for measures, calculated columns, and calculated tables.
Common categories
- Aggregation
SUM(), AVERAGE(), COUNT(), DISTINCTCOUNT() - Filter / context
CALCULATE(), FILTER(), ALL(), ALLEXCEPT() - Time intelligence
DATEADD(), TOTALYTD(), SAMEPERIODLASTYEAR(),DAY(),MONTH() - Logical
IF(), SWITCH(), AND(), OR(), NESTEDIFS - Lookup / relationships
RELATED(), LOOKUPVALUE(), USERELATIONSHIP() - Table functions
VALUES(), SUMMARIZE(), ADDCOLUMNS()
Used when: you’re creating KPIs, ratios, YoY growth, rolling averages, etc
- Power Query (M) functions (Data transformation) Used in Transform Data before the model loads.
Common categories
- Text
Text.Upper, Text.Lower, Text.Trim, Text.Contains - Date/Time
Date.Year, Date.Month, Date.AddDays - Number
Number.Round, Number.Abs - Table
Table.SelectRows, Table.AddColumn, Table.Merge - List
List.Sum, List.Distinct - Conditional logic
if … then … else
Used when: cleaning data, shaping columns, merging tables, fixing formats.
- Power BI visual-level “functions” Not written as code, but still important.
- Visual aggregations (Sum, Avg, Min, Max)
- Filters (Top N, Relative Date)
- Drill-down / drill-through
- Tooltips
- Conditional formatting
- Visual calculations (newer feature)
Used when: shaping how data appears on the report.
Joins and Relationships in Power BI
Joins - This is primarily achieved through Merging Queries in Power Query – Go Analytics Merge Queries in Power Query (Excel/Power BI) joins two tables based on common columns, similar to a SQL JOIN, to combine related data.
Found in the Home tab, it offers options to merge into an existing query or as a new one. It supports various join types, including Inner, Left Outer, and Full Outer.
*Relationships * - are the connections between tables that allow you to analyze data across multiple sources as if they were one. Think of them like bridges that link different datasets together so you can build meaningful reports and visuals.
Data modeling concepts and best practices
- The data modelling process involves six steps:
- Identifying business entities
- Defining key properties
- Creating a draft ER model
- Identifying data attributes
- Mapping attributes to entities
- Finalizing and validating the data model
Visualizations overview in Power BI
- Power BI offers a wide variety of built-in visuals, each designed for specific scenarios and data types.
- Visuals in a report interact with each other through cross-filtering and cross-highlighting, creating a fully interactive and dynamic experience. You can also drill through to other pages or even other reports to explore related data in more detail.
Charts for comparison and trends
- Bar and column charts Bar and column charts are the standard for comparing specific values across different categories. Use column charts for time-based comparisons and bar charts when category names are long.
- Line charts Line charts emphasize the overall shape of values over time, making them ideal for showing trends and patterns. They work best with continuous data along the X axis.
- Area charts Area charts are based on line charts with the area between the axis and line filled in. They emphasize the magnitude of change over time and can show cumulative totals when stacked.
- Combo charts Combo charts combine a column chart and a line chart into one visual, allowing you to compare multiple measures with different value ranges. They can have one or two Y axes.
Ribbon charts
Ribbon charts show which category has the highest rank over time, with the highest value always displayed on top for each period. They're effective for visualizing rank changes.Waterfall charts
Waterfall charts show a running total as values are added or subtracted. They're useful for understanding how positive and negative changes affect an initial value, such as tracking contributions to net income.
Charts for part-to-whole relationships
- Pie and donut charts Pie and donut charts show the relationship of parts to a whole. Donut charts have a blank center that allows space for a label or icon. Use these charts when you have a small number of categories.
- Treemaps Treemaps display hierarchical data as nested rectangles, with size representing value. They're excellent for showing large amounts of hierarchical data and the proportions between parts and the whole.
- Funnel charts Funnel charts visualize sequential processes with stages, where items flow from one stage to the next. They're commonly used for sales pipelines and conversion tracking.
Charts for distribution and relationships
- Scatter, bubble, and dot plot charts Scatter charts display data points at the intersection of two numerical values, revealing correlations and clusters. Bubble charts add a third dimension through bubble size, while dot plots can use categorical data on the X axis.
- Tables and matrices These visuals display detailed data in rows and columns.
Tables
Tables present related data in a grid of rows and columns. They're ideal when you need to see exact values and make quantitative comparisons across many values for a single category.
Matrix visuals
Matrix visuals support stepped layouts and make it easier to display data across multiple dimensions. They automatically aggregate data and enable drilling down into hierarchies.
Map visualizations
Map visuals help you display geographic and spatial data. Power BI offers several map types for different scenarios.
Cards or callouts, KPIs, and gauges
These visuals help you highlight key values and track progress toward goals and key metrics.
- Card visuals Card visuals display a single fact or data point prominently. The card visual supports both single-card and multi-card layouts, and can include images and detailed reference values within the visual. Use cards when a single number, such as total sales or market share, is the most important thing to track.
KPI visuals
Key performance indicator (KPI) visuals communicate progress made toward a measurable goal. They're ideal for measuring progress and distance to a metric.Gauge charts
Radial gauge charts display a single value measuring progress toward a goal. The needle represents the target value, and shading shows progress. They're great for showing the health of a single measure.Goals visual
The goals visual displays metrics and scorecards that help teams track progress toward business objectives. You can set targets, track current values, and visualize status with color-coded indicators.
AI-powered visuals
- These visuals use artificial intelligence to help you explore and understand your data. Decomposition tree Decomposition tree visuals let you visualize data across multiple dimensions. They automatically aggregate data and enable drilling down in any order, with AI helping identify the next dimension to explore.
Key influencers
Key influencer charts display the major contributors to a selected result or value. They help you understand which factors influence a key metric.
Anomaly detection
Anomaly detection automatically finds anomalies in line chart data. It highlights unexpected spikes and dips, helping you identify outliers that need investigation.
Filtering visuals
Power BI provides multiple ways to filter data in your reports. All visuals can be filtered using the Filters pane, which lets you apply filters at the visual, page, or report level. For more direct interaction, slicers provide on-canvas filtering controls.
Slicers
Slicers come in multiple formats including button, list, dropdown, and date range. They display commonly used filters on the report canvas for easier access.
Other visuals
Image visual
Text box and shapes
Buttons and navigators
Paginated report visual
Q&A visual
R and Python visuals
Power Apps visual
Custom visuals
Choosing the right visual
- Your data type: Categorical, numerical, time-based, or geographic
- Your goal: Compare values, show trends, display relationships, or track progress
- Your audience: What level of detail do they need?
- Available space: Some visuals work better in smaller or larger areas
Dashboard design and layout principles
Consider your audience
Tell a story on one screen
Make use of full-screen mode
Accent the most important information
Place the most important information
Use the right visualization for the data
**Data storytelling and translating insights into action
**
- Data storytelling is very similar to human storytelling but provides the added benefits of deeper insights and supporting evidence through graphs and charts.
- Through data storytelling, complicated information is simplified so that your audience can engage with your content and make critical decisions quicker and more confidently.
Making sure your data story is valuable
Think about your theory - What do you want to prove or disprove? What do you think the data will tell you?
Collect data. Collate the data you’ll need to develop your story.
Define the purpose of your story- Using the data you gathered, you should be able to write what the goal of your story is in a single sentence.
Think about what you want to say- Outline everything from the intro to the conclusion.
Ask questions. Were you right or wrong in your hypothesis?- How do these answers shape the narrative of your data story?
Create a goal for your audience- What actions would you like them to take after reading your story?
The key components of data storytelling
- Use data as the foundation of your story
- Create visuals to make insights clear
- Craft a narrative that connects data and visuals
Top comments (0)