We are the Best Consulting web site as part of the annual WebAward Competition!

(832) 981-4635
info@datacrafters.io
img
Language

English

Getting Started with Power BI: Step by Step Guide

How do you get benefits out of it? Remember Two things:

1. Action, get started todays

  • Seven times Seven ways
  • What am I going to learn today and how is that going to make an impact TODAY

2. Quick Wins, aim for quick small wins and ITERATE

  • Read the value of quick wins and steps to avoid pitfalls.

Data Prep/Power Query

ActivityBeginnerIntermediateAdvanced
Data sourceManual

The source data may be manually compiled by the author, usually for testing purposes.
Connected to the source system

The source data is queried from the proper source system.
Efficiency and optimization

Queries are optimized for efficiency, such as query folding when querying the source system.
Data refreshManual

The dataset is manually refreshed in Power BI Desktop by the dataset author.
Scheduled

The dataset is scheduled in the Power BI Service, so it refreshes automatically.
Incremental refresh

The dataset refresh operation is optimized for incremental data refresh.

Data Modelling/DAX

ActivityBeginnerIntermediateAdvanced
Table StructureFlat Table

Remarkably similar to PivotTable Data in Excel, where you used lookups in the source (Most likely Excel or current source).
Dimensional Relationship

Fact-Dimension, Star Schema, Calendar Table.
Optimized Storage & Relationship

Wide and Short Dimension Table & Skinny and long Fact Table Read More
DAXBasic Measures

Get started with drag and drop, but highly recommend writing Explicit Measures. Read More Sales = SUM( ‘Invoice Data'[Revenue])
Calculate/ Time Intelligence

Function: Calculate/Filter, Time Intelligence (DateAdd, DatesYTD)
Introduction to X Functions,
Filter Context etc.
Incremental refresh

DAX/Measure Optimization, unlocking the power of Calculate/Filter/Iteration/Table Functions, Composite Models
External Tools
1) DAX Studio 3) Bravo
2) Tabular Editor

Visualization

ActivityBeginnerIntermediateAdvanced
Technical

Follow K.I.S.S method

Simple Tables/Matrix and Charts. Let the interactivity of Power BI along with Drill Down do the magic, just getting what you have replicated.
Move beyond The Grid

Drill Through, Information canvas, attention direction flow, information positioning, color contrast, conditional formatting.
Sprinkle on Top/Insight

Bookmarks, Navigation, advanced custom visuals (Inforiver, Zebra BI).
Every Custom Visual has its own learning curve.
Being able to synthesize information into simple nuggets. IBCS

1. Date Prep: Power Query/M language/Get Data to transform and Load

    • Beginner: Data Source is Manual and needs supervision/modification for a refresh.
    • Intermediate: Connected to Source System and Refreshes Automatically.
    • Advanced: Optimized refresh time/Incremental refresh with efficient load distribution source/model.

2. Modeling: Model/DAX/Relationship Semantic Layer.

    • Beginner: Flat Table very similar to Pivot Table, try to write simple Measures like SUM, Calculate.
    • Intermediate: Measures, Fact-Dimension, Star Schema, Calendar table-based Time Intelligence, Dabbling in Calculate/Filter/X functions.
    • Advanced: DAX/Measure Optimization, unlocking the power of Calculate/Filter/Iteration/Table Functions, Composite Models, Nested functions.

3. Visualization: The Art/Science of Story Telling.

    • Beginner: Simple Tables/matrix, Cards, and Charts, very similar to Excel.
    • Intermediate: Move beyond the Excel Grid concept to information canvas, basics of storytelling, positioning, and contrast.
    • Advanced: Bookmarks, Navigation, advanced custom visuals, Design visuals clear and catchy.
    • Custom Visuals: You can benefit from them, however, each one of them has a learning curve.

Here goes nothing. I’ll try to lay down my method to the madness for success/adoption/efficacy with Power BI.

Date PrepModelingVisualization
Iteration 1BeginnerBeginnerBeginner
Iteration 2BeginnerBeginnerIntermediate
Iteration 3BeginnerIntermediateIntermediate
Iteration 4BeginnerIntermediateIntermediate
Iteration 5IntermediateIntermediateIntermediate
Iteration 6IntermediateAdvancedAdvanced
Iteration 7IntermediateAdvancedAdvanced
Iteration 8AdvancedAdvancedAdvanced

ActivityPurposeKey Points
Iteration 1Amaze yourself that you built an interactive dashboard in less than a couple of hours.

1. Make sure you build/convert one of your current reports or dashboards 

2. Do not share this version
Iteration 2Amaze/tease your consumers and collect feedback.1. Ensure your report is presentable and learn to embrace the blank space, DO NOT PUT too many things just because you can

2. Ensure your report is presentable and learn to embrace the blank space, DO NOT PUT too many things just because you can: **Win people over with the power of familiarity, Use Power BI Secret weapon Analyze in Excel.
Music and Emotions in the Brain : Familiarity Matters 

3. I know your sources are still manual, so not that much capacity has opened up, but you’ll get the boost from the win.
Iteration 3Ease of communicating messages and unlock dynamic calculations for new capabilities. 1. Move beyond VLOOKUP/Index-Match to the power of relationship and introduce the Calendar table.

2. Significant benefit by having Time Intelligence and dimensionality.

3. Share it with your consumers, do not forget their adoption speed would vary and continue to use the power of familiarity in Analyze in Excel.

4. Still Manual Source, but Start investigating access to source/auto-refresh capabilities.
Iteration 4You should completely replace and replicate your old report.

1. Ensure you have parity and superiority to the old report.

2. Cut off the old report to open capacity.

3. Models and Visualization will continue to be improved; however, you don’t want to get stuck in doing both methods.

4. At this point your Effective way should first go for data modeling and then visualizations.
Iteration 5As we are Effective now, focus on Efficiency 1. Date Auto Refresh Strategy: Best Solution: Direct access to source ERP/CRM etc. or any available Data Warehouse.
Means working with IT to get access, it’s hard but you must communicate with IT frequently to get access.
If that fails, focus on if there are automated reports that is being sent out, it’s a form of batch jobs.
You can store them preferably in SharePoint Folder and have Power BI refresh from there.
You can use Flow/Power Automate to connect and get output automatically as you want.

2. Why did we not evaluate Automation earlier? 
Primarily because it takes a lot of time and is a bureaucratic process. 
Honestly, your consumers DO NOT CARE as you have nothing to show them.
It is particularly important to achieve automation for sustainability, but this is not a battle you pick first if you want to win the war. 
Also, if you do not free yourself up at this point, you cannot focus on bigger and more complex requests from your consumer.
Iteration 6Utilize more advanced design/modeling capability to solve questions that you couldn’t before. 1. Higher ROI than visualization as answers more complex questions.

2. This step might come early if you have hundreds of Millions/Billions of records.

3. Do not complicate it if there is no value.
Iteration 7Focus on visual finesse and wow your consumers.

1. At this iteration, you have answered the questions but now you can focus better on storytelling to effectively communicate the insight.

Iteration 8The journey does not stop as there is always a new question to answer or a change in leadership that wants a different color……. 

1. Just remember what success really looks like, so, be open to change and adapt. 

Iteration 1:
Purpose: Amaze yourself that you built an interactive dashboard in less than a couple of hours.

Key Points:

  • Make sure you build/convert one of your current reports/dashboards.
  • Do not share this version.

Iteration 2:
Purpose: Amaze/tease your consumers and collect feedback.

Key Points: 

  • Ensure your report is presentable and learn to embrace the blank space, DO NOT PUT too many things just because you can.
  • Share it with your consumers that are more open to change
    Win people over with power of familiarity, Use Power BI Secret weapon Analyze in Excel.
  • Music and Emotions in the Brain: Familiarity Matters
  • I know your sources are still manual, so not that much capacity has opened, but you will get the boost from the win.

Iteration 3:
Purpose: Ease of communicating messages and unlock dynamic calculations for new capabilities.

Key Points: 

  • Move beyond VLOOKUP/Index-Match to the power of relationship and introduce Calendar table.
  • Significant benefit by having Time Intelligence and dimensionality.
  • Share it with your consumers, do not forget their adoption speed would vary, and continue to use the power of familiarity in Excel.
  • Still Manual Source but Start investigating access to source/auto-refresh capabilities.

Iteration 4: 
Purpose: Should completely replace and replicate your old report.

Key Points:

  • Ensure your parity and superiority to the old report.
  • Cut off the old report to open capacity.
  • Models and Visualization will continue to be improved; however, you don’t want to get stuck in doing both methods.
  • At this point you are Effective.

Iteration 5:
Purpose:  As we are Effective now, focus on Efficiency.
Key Points:  Date Auto Refresh Strategy:

Best Solution: Direct access to source ERP/CRM etc. or any available Data Warehouse

  • It means working with IT to get access, but it is hard.
  • If that fails, focus on if there are automated reports that are being sent out, it is a form of batch jobs.
  • You can store them preferably in SharePoint Folder and have Power BI refresh from there
  • You can use Flow/Power Automate to connect and get output automatically as you want.

Why did we not evaluate Automation earlier? 

  • Primarily because it takes a lot of time and bureaucratic process.
  • Honestly, your consumers DO NOT CARE as you have nothing to show them.
  • It is very Important to achieve automation for sustainability, but this is not a battle you pick first if you want to win the war.
  • Also, if do not free yourself up at this point, you cannot focus on bigger and more complex requests from

Iteration 6:
Purpose: Utilize more advanced design/modeling capability to solve questions that you couldn’t before.

Key Points: 

  • Higher ROI than visualization as answers more complex questions.
  • This step might come early if you have hundreds of Millions/Billions of records.
  • Do not complicate it if there is no value.

Iteration 7:
Purpose: Focus on visual finesse and to wow your consumers.

Key Points: 

  • At this iteration, you have answered the questions but now you can focus better on storytelling to effectively communicate the insight.

Iteration 8:
Purpose: For Large and complex Data Engineering, optimize load time and stress on system.

Key Points:

  • The problem of the very few who have that large data, however very important again for sustainability to maintain capacity.

Iteration 9 & on:
Purpose: Journey does not stop as there is always new question to answer or change in leadership that wants a distinct color …