Power BI Core Concepts & Workflow Cheatsheet

Power BI Core Concepts & Workflow Cheatsheet 📊

Module 1: Introduction to Microsoft Data Analytics & Power BI

Data Analytics Roles: Focus on Data Analyst responsibilities.

Power BI Licensing:

Free: Basic features, personal use.

Pro: Collaboration, sharing, content packs.

Premium: Dedicated capacity, larger datasets, advanced features (like paginated reports).

Power BI Suite: Includes Power BI Desktop, Power BI Service, Power BI Mobile.

Module 2: Getting Data 📥

Common Data Sources:

Excel Workbooks

Access Databases

Web (Online Sources)

Connectivity Modes:

Import: Data loaded into Power BI. Faster queries, but data is static until refreshed.

DirectQuery: Live connection to the source. Data is always current, but performance depends on the source.

Key Terms:

Data Source: Where the data originates.

Data Set: Data prepared for use in Power BI reports and dashboards.

Error Handling: Address missing source files, issues with Power View visuals.

Performance Tip: Cloud-stored files (OneDrive/SharePoint) offer better refresh capabilities than local files.

Module 3: Shaping Data (using Power Query Editor) 🛠️

Purpose: Clean, transform, and reshape data before loading.

Common Transformations:

Rename Tables/Columns

Promote First Row as Headers

Remove Columns

Filter Rows (e.g., remove nulls, specific values)

Sort Data

Merge Queries: Combine data from multiple tables (like SQL JOINs).

Data Profiling Tools: Assess data quality (column quality, distribution, profile).

Module 4: Data Modeling Fundamentals 🧱

Goal: Design efficient and scalable data models.

Table Operations:

Format Columns: Set data types (text, number, date), currency, percentages.

Categorize Data: For mapping (e.g., address, city, latitude, longitude).

Organization:

Dimensions: Descriptive attributes (e.g., Product, Customer, Date).

Hierarchies: Navigational paths (e.g., Year > Quarter > Month).

Model Relationships:

Define how tables connect.

Cardinality:

One-to-One (1:1)

One-to-Many (1:N) - Most common

Many-to-One (N:1)

Many-to-Many (N:N) - Use bridge tables if possible.

Security:

Row-Level Security (RLS): Restrict data access for specific users/roles.

Module 5: DAX Calculations (Data Analysis Expressions) 📈

DAX: Formula language for creating calculated columns, measures, and tables.

Key Concepts:

Calculated Columns: Computed row by row, stored in the model. Use for static values or row-context logic. [Column] = [Price] * [Quantity]

Measures: Aggregations calculated on the fly based on report context (filters, slicers). Do not store data. Total Sales = SUM(Sales[Amount])

Calculated Tables: Tables created using DAX formulas.

Quick Measures: Pre-built common calculations.

Common DAX Functions:

Aggregation: SUM(), AVERAGE(), MIN(), MAX(), COUNT(), DISTINCTCOUNT()

Logical: IF(), SWITCH()

Filter/Context: CALCULATE(), FILTER(), ALL(), ALLEXCEPT(), ALLSELECTED()

Date/Time (Time Intelligence): Often requires a Date Table.

DATEDIFF(): Difference between two dates.

CALENDARAUTO(): Creates a date table automatically.

YEAR(), QUARTER(), MONTH(), DAY()

FORMAT(): Format dates/numbers.

ENDOFMONTH(), ENDOFQUARTER()

TOTALYTD(), SAMEPERIODLASTYEAR()

Ranking: RANKX()

Table Manipulation: ADDCOLUMNS(), DISTINCT()

Key Performance Indicators (KPIs): Visual representation of progress towards a measurable goal.

Module 6: Optimizing Model Performance 🚀

DirectQuery: Consider for very large datasets or real-time needs (be mindful of source performance).

DAX Variables: Use VAR ... RETURN syntax in DAX measures for better readability and performance (avoid repeating calculations).

Optimization Techniques:

Restrictive Filters: Apply filters early in data import or visuals.

Limit Visuals: Avoid too many visuals on one page.

Evaluate Custom Visuals: Some custom visuals can impact performance.

Reduce cardinality of columns where possible.

Optimize data types.

Module 7: Designing and Building Reports 🖼️

Visual Selection (Examples):

Pie Chart/Donut Chart: Parts of a whole.

Clustered/Stacked Column/Bar Chart: Comparisons.

Multi-row Card: Display multiple key metrics.

Table/Matrix: Detailed data, crosstabs.

Slicer: Interactive filtering.

Scatter Chart: Relationship between two numerical values.

Line Chart: Trends over time.

KPI Visual: Track progress against a target.

Report Design Elements:

Tooltips: Show additional info on hover.

Legends: Identify data series.

Titles, Backgrounds, Shadows: Enhance visual appeal.

Data Labels: Display values directly on visuals.

Sorting & Filtering: Control data presentation.

Advanced Features:

Sync Slicers: Apply a slicer across multiple report pages.

Drill-through Pages: Navigate to a detailed page filtered to a specific data point.

Conditional Formatting: Highlight data based on rules (e.g., color cells in a table).

Bookmarks: Save specific report states (filters, visuals) for navigation or storytelling.

Accessibility:

Alt Text: Descriptions for visuals (for screen readers).

Tab Order: Define navigation order for keyboard users.

Themes: Use high-contrast themes.

Customization:

AppSource Custom Visuals: Extend visualization options.

Copy Visualizations: Duplicate visuals within or between pages.

Module 8: Creating Dashboards (in Power BI Service) 🖥️

Purpose: Provide a high-level, consolidated view of key metrics from multiple reports.

Creation: Pin Visualizations from published reports to a dashboard.

Dashboard Enhancements:

Add Videos, Text Boxes, Images, Web Content.

Apply Themes.

Key Features:

Tile Alerts: Notify users when data in a tile meets a specific threshold.

Q&A (Question and Answer): Ask natural language questions about your data.

Quick Insights: AI-powered feature to automatically find interesting patterns in your data.

Management:

Set a Featured Dashboard (default landing dashboard).

Manage Favorite Dashboards.

View Usage Metrics to see how dashboards are being used.

Mobile View: Configure dashboards for optimal viewing on mobile devices.

Module 9: Paginated Reports 📄

Purpose: Designed for printing or PDF export; ideal for operational reports, invoices, or when you need to see all data (can span many pages).

Key Characteristic: "Pixel-perfect" layout.

Availability: Requires Power BI Premium capacity for the workspace.

Authoring Tool: Power BI Report Builder (a separate standalone application).

This cheatsheet should give you a good overview of the key topics covered in the Power BI course!

Sum all digits of a number

Here below find the code to sum all the digits of a number

Subprocedure will ask an input and return a message box of sum.

Sub sum_digits()

num = InputBox("Enter digits to sum")

    Do While Number >= 1

       ss = ss + num Mod 10

        num = Int(num / 10)

    Loop

    MsgBox ss

End Sub

Asterisk Triangle in Excel VBA Through Loops

In below code you can see how to create different Triangles of asterisk in Excel VBA through Loops.

You can change the value in Cells if you want different than asterisk.

This is one of the most asked interview questions about excel vba.

Code used to create above

Sub loop_test1()
    For i = 1 To 20
        For j = 1 To 21 - i
        Cells(i, j).Value = "*"
        Cells(i, j).Interior.Color = vbYellow
        Next
    Next
    Range("A1").CurrentRegion.Select
    Selection.Columns.AutoFit
End Sub

Code for Loop Test 2 used to create above triangle

Sub loop_test2()
    For i = 20 To 1 Step -1
        For j = 1 To i
        Cells(i, j).Value = "*"
        Cells(i, j).Interior.Color = vbYellow
        Next
    Next
    Range("A1").CurrentRegion.Select
    Selection.Columns.AutoFit
End Sub

Code for Loop test 3 (smallest triangle)

Sub loop_test3()
    For i = 1 To 3
        For j = 4 - i To i + 2
        Cells(i, j).Value = "*"
      Cells(i, j).Interior.Color = vbYellow
        Next
    Next
    Range("A1").CurrentRegion.Select
    Selection.Columns.AutoFit
End Sub

Code created the above Pyramid is here

Sub loop_test4()

    For i = 1 To 10

        For j = 10 - i + 1 To i + 9

        Cells(i, j).Value = "*"

      Cells(i, j).Interior.Color = vbYellow

        Next

    Next

    Range("J1").CurrentRegion.Select

    Selection.Columns.AutoFit

End Sub

Consolidation of Data from folders and subfolders using recursion

Option Explicit
Dim wb As Workbook
Dim lr As Long
Dim lrt As Long
Sub GetAllFiles()

    Dim Directory As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Select a location containing the files you want to list."
        .Show
        If .SelectedItems.Count = 0 Then
            Exit Sub
        Else
            Directory = .SelectedItems(1) & "\"
        End If
    End With
 
    Cells.ClearContents
    Call RecursiveDir(Directory)
End Sub

Public Sub RecursiveDir(ByVal CurrDir As String)
    Dim Dirs() As String
    Dim NumDirs As Long
    Dim FileName As String
    Dim PathAndName As String
    Dim i As Long
    Dim Filesize As Double

'   Make sure path ends in backslash
    If Right(CurrDir, 1) <> "\" Then CurrDir = CurrDir & "\"

'   Get files
    On Error Resume Next
    FileName = Dir(CurrDir & "*.*", vbDirectory)
    Do While Len(FileName) <> 0
      If Left(FileName, 1) <> "." Then 'Current dir
        PathAndName = CurrDir & FileName
        If (GetAttr(PathAndName) And vbDirectory) = vbDirectory Then
          'store found directories
           ReDim Preserve Dirs(0 To NumDirs) As String
           Dirs(NumDirs) = PathAndName
           NumDirs = NumDirs + 1
        Else
  Set wb = Workbooks.Open(PathAndName)
   lr = ActiveWorkbook.Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
   Range("A2:D" & lr).Copy
   lrt = ThisWorkbook.Worksheets("Sheet1").Range("A1048576").End(xlUp).Row + 1
   ThisWorkbook.Worksheets("Sheet1").Range("A" & lrt).PasteSpecial xlPasteAll
   wb.Close
   End If
    End If
        FileName = Dir()
    Loop
    ' Process the found directories, recursively
    For i = 0 To NumDirs - 1
        RecursiveDir Dirs(i)
    Next i
End Sub

ActiveX Data Objects (ADO)

Active X Data Objects (ADO)

Add Below Library to use ADO

Option Explicit

Sub copyfromdb()
Dim salesconn As ADODB.Connection
Dim rst As ADODB.Recordset
Set salesconn = New ADODB.Connection
Set rst = New ADODB.Recordset
salesconn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\sid\Desktop\Movies.accdb;Persist Security Info=False"

salesconn.Open

With rst
.ActiveConnection = salesconn
.Source = "select region from sales"
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Worksheets.Add
Range("A1").CopyFromRecordset rst.Fields
Range("A1").CopyFromRecordset rst
rst.Close

salesconn.Close

End Sub

File System Object (FSO)

FSO (File Sytstem Object)

Add Library to use early binding 

Option Explicit

Sub fso()
Dim fso As FileSystemObject
Dim name As String
Dim fldr As String
Dim rng As Range
Dim cell As Range
Dim ts As TextStream
Dim folder As folder
name = Format(Date, "mm-dd-yyyy")
fldr = "C:\Users\pc Admin\Desktop\" & name

Set fso = New FileSystemObject

If Not fso.FolderExists(fldr) Then
Set folder = fso.CreateFolder(fldr)
Else

End If
If Not fso.FileExists(fldr & "\My File.txt") Then
folder.CreateTextFile ("My File.txt")
End If
Set rng = Range("A1").CurrentRegion
Set ts = fso.OpenTextFile(fldr & "\My File.txt", ForWriting)

For Each cell In rng
ts.WriteLine cell
Next

End Sub