Power BI Desktop
- Connect with all data that is important to you
- Easily prepare and model data
- Combine advanced analysis with the familiarity of Excel
- Create interactive reports tailored to your business
- Anyone can design anywhere
With Power BI Desktop, you connect to data (usually multiple data sources), shape that data (with queries that build insightful, compelling data models), and use that model to create reports (which others can leverage, build upon, and share).
Connecting to a Database and Transformations
- Power BI à Get Data à Select Required Tables
- Transformations (Many exists, few samples provided here)
- Date type is determined by sampling 100 rows. Data type could be changed manually
- Delimit data from single column to multiple columns. Source column could be removed
- Many functions could be applied on columns level (Date, Time, Numeric Functions)
- Add conditional column to use for case when operation
Transformation steps are sequentially listed / recorded into right pane of Applied Steps.
- On Query Editor left pane when you have many tables. Create query group and categorize them into right folders for better visibility and readability.
- Simple drag and drop into folders. Query groups such as Dimensions, Facts, Functions, Data Source System, etc..
Connecting to Files and Folders
- Use get data to choose a folder consist of multiple csv files.
- Read all files names from a folder by using folder connection. Get standard fields such as Name, Extension, Date Accessed, Date Modified, Date Created, Folder path, Attributes.
- Combine all files data and load into Power BI / Select files to load into Power BI from source folder
- Picture shows multiple files loaded into Power BI Desktop
- Click content to import data from all files then as part of row transformation merge data from files from other sales table using Append transformation. Additional columns are created in case mismatch between the two sources.
Transformations in Power BI
Simple actions from Power BI Desktop with interactive menus and wizards.
- Change Data Type
- Fill Down and Up
- Pivot and Unpivot
- Split and Merge
- Extract and Format Text
- Date and Time
- Structured Column
- Add Index and Duplicate Column
- Add Column Tab vs Transform Tab
- Add Conditional Column
- Add Custom Column
- Append Data (union)
- Merge Data (join)
Provides a graphical representation of dependencies between queries. When a particular table is selected, all its related other tables are highlighted. View Query Dependencies from Query editor.
Related objects and transformations are highlighted when a single object is selected in the chart
Less Structured Data and Create Manual Table
- Less structured excel pivot data, could be easily transformed into data tables in Power BI. Typically, a transpose, choosing column header, un-pivot / pivot, fill down/up helps to create data tables from less structured data
- When required for manual data table, create a manual static table with data by navigating Home and Enter data button. You can also copy paste table data into Power BI
- Use query parameters to choose filtered data for reporting. Works while opening pbix file and from query editor
- From query editor use new parameter to configure parameter. On data table / query use this parameter to filter data
Power BI Data Modelling
- Select relationships tab to view data model of underlying tables
- Creating relation is simply drag and drop and by using,
- Manage relations
- to auto detect relations.
- Edit relation option to create relations manually
- Apply Cardinality / cross filtering
- Hide tables or columns from model from data model canvas
- Calculated Columns
- Add new columns from modelling ribbon. Use DAX functions to create functions. Function window is intellisense enabled with suggestions.
- Optimizing Models for Reporting
- Hide unnecessary attributes / fields from right pane of fields. Apply sort by column to select right order columns. Apply formatting of selected fields
Calculated Measures – YTD
- Add DAX calculated measures from Home à New Measure. Move the measure to other home table.
- Year To Date Revenue
- YTD Revenue = TOTALYTD(sum(Sales[Revenue]), ‘Date'[Date])
- Description: Year to Date Revenue (Running Revenue) is sum(sales.Recenue) over date
- Last Year YTD and Variance
All four formula required for YTD Sales Var% is list in the above picture.
Creating and Managing Hierarchies
- Create Hierarchies by dragging and dropping related fields on one another to create hierarchy. Move the sequence either by drag drop or from menu up and down
- Use drill down or drill up action buttons from Data / Drill or on visualization.
- See data by choosing see records of specific granularity
- Create derived tables from other tables.
- Enable time intelligence on date fields without a hierarchy
- Include / Exclude values from visualization by right click. This will apply filter for selected values.
- Group / Ungroup values from visualization
Power BI Visualization – Chart properties
Power BI Visualization consolidated chard properties is presented here. This provides chart property comparison. By looking at the properties, functionality of the chart could be understood.
|Visualization Charts||Primary Scale||Secondary Scale||Distribution|
|Bar, Line, Ribbon Chart||Axis||Legend||Values|
|Pie and Donut||Legend||Details||Values|
|Line and Stacked bar chart||Shared Axis||Column Series||
|Ribbon Chart||Shared Axis||Column Series||
|Waterfall Chart||Category||Breakdown||Y Axis|
|Scatter Plot||Details, Legend and X Axis||
Y Axis and
Play Axis (Animation)
|Filled Map||Location||Legend||Latitude and Longitude|
|Gauge||Min Value||Max Value||Value and Target Value|
|KPI||Trend Axis||Target||Indicator and Target goals|
|Map||Location||Legend||Lat, Long and Size|
Chart Functions and Features
Filter Options – Could be applied Per Chart / Page / Report
- Basic Filter – to Include / exclude results
- Advance Filter – to search for text contains
- Top / Bottom x values
- Date and Numerical Values for corresponding data type fields
Key Formatting Options
- Hierarchical Axis – Typically hierarchical axis consist of hierarchy headers concatenated leads to small text format. Select X / Y Axis formatting and switch concatenate labels off
- Add min, max, avg, medial and forecast lines
- Use auto cluster detection on scatter plot and table chart. Plot clusters to a separate chart
- Focus chart canvas, use see data menu and export data to csv
- Map chart
- Use lat long for exact location in case of same city names in different countries
- Use Data category to choose State / City when city and state names are the same
- Use ArGIS for advanced map charts
- Tables – Conditional Formatting, Table styles
- Page Size and Layouts
- Switch On/Off Visual interaction during value selections
- Show Items with no data
- Change default summarization from sum to avg.
Power BI Service (Cloud Service https://app.powerbi.com)
- Cloud service to publish and share dashboards and reports
- Quick insights
- On any dataset select quick insights which generates insights reports automatically – for quick auto reports
- Auto reports could be saved as new / existing dashboards. Also, get multi level of insights from a specific dashboard by selecting successive get insights
- Dashboard Features
- Flexible and quick dashboard configuration. Add video, web content, image and text
- Set Featured / Favorite dashboards Share dashboard to others
- Subscribe to dashboard. Manage frequency of delivery. Edit titles, place custom URL
- Full screen, navigation collapsed (to add in URL ?collapseNavigation=true) Chrome less Mode
- Print Dashboard. Export Data to CSV from Dashboard / Report.
- Power BI Notifications and Alerts (If values reach a threshold). Gateway setup. Publish reports to other web sites
- Admin portal for usage metrics and performance scaling (only for premium license)
Natural Language Query
Working with Excel
- Upload Excel data into Power BI service and create reports on top of this data
- Update Excel file with Power Pivot, Power Pivot to Power BI. Power BI will automatically retain Power pivot models and power view reports
- Connect Power BI Service with Excel from Sharepoint or Onedrive
- Pin Excel tables as visuals from Power BI Service / Excel
- Analyze Power BI Dataset into Excel by downloading SQL as OleDB Connection
Organization Packs, Security and Groups
- Create a App workspace, in-turn an office 365 group will be created for user management
- Create a content pack to group related dashboards, reports and dataset. This content pack could be shared to entire organization / specific groups.(From settings click content pack)
- Other members get My organization content pack from get data menu
- Members get My organization content pack from get data menu
- Content pack dashboard changes need to updated after editing dashboard. This could be done from view content pack and click update after changing the dashboards
- Onedrive for Business Integration – power bi files could be stored in onedrive and pulled from power bi service
- Data Classification – Use data classification page set classify reports into High Business Impact Reports (HBI), MBI and LBI. This will help users to understand the criticality of the dashboards / reports before sharing to others.
Row level Security – Setup Roles and add user management
- From Power BI Desktop, modelling ribbon select Manage roles to create multiple roles with corresponding DAX filter expression.
- From Power BI service, select the roles defined in Power BI and add members for the particular roles
Power BI Reports Data Classification
- Classify reports into custom categories to provide confidentiality / criticality / other class of reports. The classification could be created from admin portal à Tenant Settings à Data classification.
- Helps to tag reports into right classification.
- Helps user to understand the classification before sharing
Picture shows classification creation and Tagged reports based on selected classifications
- Directly connect to multiple other sources
- Only the definition is imported into Power BI.
- No need to schedule refresh / refresh now. Every time a report is viewed, data gets reflected from underlying data source.
- Calculated Measures could be created on top of data source
- Relations could be created on top of data source
- Interface with Power BI using APIs.
- Visit https://powerbi.docs.apiary.io for interactive tutorials and hands on to interface Power BI with API. Create dataset, tables, rows programmatically
- Dashboard tiles could be embedded to any web services by API. PowerBI-Csharp repository from Github
- Using PubNub to push data to a tile. Insert real time (Sensor /IoT) data into Power BI Dataset
- Create Custom Visuals using TypeScript.
- App.powerbi.com/visuals – Power BI Visual gallery to check all the custom visuals created by people
- Power BI Embedded – Completely hide underlying Power BI Technology. Shows only the functional part how it could be used as other application.
Custom Visual – app.powerbi.com/visuals
There are whole lot of custom visuals are available from Power BI visuals library. This could be easily used on power bi reports. One of the sample is shown here. This custom visual consist synoptic panel (left hand side) and Chord chard (right hand side)
Left chart shows an Electronic Store Layout. When a particular store is selected, right hand chart shows the products that bought together at this store.
Power BI Mobile App
- Change Web view to Phone view on Dasboard.
- Take Mobile content to offline when there is no internet connection
- Platform specific features
- Windows phone have option to pin dashboard to home screen
- Option to set value alerts exist in iPhone.
- Limit / manage mobile access from portal.azure.com
Disclaimer: These are my personal learning from edx.