Power BI Guide

Step 1: Adding Dataset

1a. Python

Download Python: https://www.python.org/downloads/ (skip if you have Python).

Check Python installation:

Open Command Prompt (cmd) > Type: where python

Install necessary packages:

"C:\Program Files\Python312\python.exe" -m pip install pandas openpyxl

Set Python home directory:

File > Option and Settings > Option > Python Scripting > Set Python home directory to the folder containing python.exe (e.g., "C:\Program Files\Python312")

Running Python in Power BI:

Copy and paste this in the Python script:


import pandas as pd

states = [
    ("Alabama", "AL", 32.806671, -86.791130),
    ("Alaska", "AK", 61.370716, -152.404419),
    ("Arizona", "AZ", 33.729759, -111.431221),
    ("Arkansas", "AR", 34.969704, -92.373123),
    ("California", "CA", 36.116203, -119.681564),
    ("Colorado", "CO", 39.059811, -105.311104),
    ("Connecticut", "CT", 41.597782, -72.755371),
    ("Delaware", "DE", 39.318523, -75.507141),
    ("Florida", "FL", 27.766279, -81.686783),
    ("Georgia", "GA", 33.040619, -83.643074),
    ("Hawaii", "HI", 21.094318, -157.498337),
    ("Idaho", "ID", 44.240459, -114.478828),
    ("Illinois", "IL", 40.349457, -88.986137),
    ("Indiana", "IN", 39.849426, -86.258278),
    ("Iowa", "IA", 42.011539, -93.210526),
    ("Kansas", "KS", 38.526600, -96.726486),
    ("Kentucky", "KY", 37.668140, -84.670067),
    ("Louisiana", "LA", 31.169546, -91.867805),
    ("Maine", "ME", 44.693947, -69.381927),
    ("Maryland", "MD", 39.063946, -76.802101),
    ("Massachusetts", "MA", 42.230171, -71.530106),
    ("Michigan", "MI", 43.326618, -84.536095),
    ("Minnesota", "MN", 45.694454, -93.900192),
    ("Mississippi", "MS", 32.741646, -89.678696),
    ("Missouri", "MO", 38.456085, -92.288368),
    ("Montana", "MT", 46.921925, -110.454353),
    ("Nebraska", "NE", 41.125370, -98.268082),
    ("Nevada", "NV", 38.313515, -117.055374),
    ("New Hampshire", "NH", 43.452492, -71.563896),
    ("New Jersey", "NJ", 40.298904, -74.521011),
    ("New Mexico", "NM", 34.840515, -106.248482),
    ("New York", "NY", 42.165726, -74.948051),
    ("North Carolina", "NC", 35.630066, -79.806419),
    ("North Dakota", "ND", 47.528912, -99.784012),
    ("Ohio", "OH", 40.388783, -82.764915),
    ("Oklahoma", "OK", 35.565342, -96.928917),
    ("Oregon", "OR", 44.572021, -122.070938),
    ("Pennsylvania", "PA", 40.590752, -77.209755),
    ("Rhode Island", "RI", 41.680893, -71.511780),
    ("South Carolina", "SC", 33.856892, -80.945007),
    ("South Dakota", "SD", 44.299782, -99.438828),
    ("Tennessee", "TN", 35.747845, -86.692345),
    ("Texas", "TX", 31.054487, -97.563461),
    ("Utah", "UT", 40.150032, -111.862434),
    ("Vermont", "VT", 44.045876, -72.710686),
    ("Virginia", "VA", 37.769337, -78.169968),
    ("Washington", "WA", 47.400902, -121.490494),
    ("West Virginia", "WV", 38.491226, -80.954456),
    ("Wisconsin", "WI", 44.268543, -89.616508),
    ("Wyoming", "WY", 42.755966, -107.302490)
]

feedback_sentiments = ["Positive", "Neutral", "Negative"]
feedback_cloud = ["great", "satisfactory", "poor", "excellent", "good", "average", "bad", "dry", "wet", "great staff"]

data = []
for i, (state, abbr, lat, long) in enumerate(states):
    sentiment = feedback_sentiments[i % len(feedback_sentiments)]
    feedback_word = feedback_cloud[i % len(feedback_cloud)]
    data.append({
        'Client ID': i + 1,
        'Client Name': f'{state} Corp',
        'Date': f'2024-{(i % 12) + 1:02d}-{(i % 28) + 1:02d}',
        'State': state,
        'Sales': (i + 1) * 1000,
        'Sentiment Score': round((i % 10) / 10, 2),
        'Feedback Sentiment': sentiment,
        'Feedback Cloud': feedback_word,
        'Latitude': lat,
        'Longitude': long
    })

df = pd.DataFrame(data)
file_path = r'C:\Users\Pawaneet Kaur\Desktop\Power BI Desktop\AllStatesSampleData1.xlsx'
df.to_excel(file_path, index=False)
print(f"Dataset created and saved as {file_path}")

After executing > select "df" under the Python folder > Close & Apply.

1b. Power Query M Script

Open Power Query Editor

Click on Transform Data in the Home tab to open Power Query Editor.

Open Advanced Editor

In Power Query Editor, click on Home > Advanced Editor.

Paste the M Script

Replace the existing code with the following script:


let
    Source = #table(
        type table [
            State = Text.Type, 
            Abbreviation = Text.Type, 
            Latitude =  number, 
            Longitude = number
        ],
        {
            {"Alabama", "AL", 32.806671, -86.791130},
            {"Alaska", "AK", 61.370716, -152.404419},
            {"Arizona", "AZ", 33.729759, -111.431221},
            {"Arkansas", "AR", 34.969704, -92.373123},
            {"California", "CA", 36.116203, -119.681564},
            {"Colorado", "CO", 39.059811, -105.311104},
            {"Connecticut", "CT", 41.597782, -72.755371},
            {"Delaware", "DE", 39.318523, -75.507141},
            {"Florida", "FL", 27.766279, -81.686783},
            {"Georgia", "GA", 33.040619, -83.643074},
            {"Hawaii", "HI", 21.094318, -157.498337},
            {"Idaho", "ID", 44.240459, -114.478828},
            {"Illinois", "IL", 40.349457, -88.986137},
            {"Indiana", "IN", 39.849426, -86.258278},
            {"Iowa", "IA", 42.011539, -93.210526},
            {"Kansas", "KS", 38.526600, -96.726486},
            {"Kentucky", "KY", 37.668140, -84.670067},
            {"Louisiana", "LA", 31.169546, -91.867805},
            {"Maine", "ME", 44.693947, -69.381927},
            {"Maryland", "MD", 39.063946, -76.802101},
            {"Massachusetts", "MA", 42.230171, -71.530106},
            {"Michigan", "MI", 43.326618, -84.536095},
            {"Minnesota", "MN", 45.694454, -93.900192},
            {"Mississippi", "MS", 32.741646, -89.678696},
            {"Missouri", "MO", 38.456085, -92.288368},
            {"Montana", "MT", 46.921925, -110.454353},
            {"Nebraska", "NE", 41.125370, -98.268082},
            {"Nevada", "NV", 38.313515, -117.055374},
            {"New Hampshire", "NH", 43.452492, -71.563896},
            {"New Jersey", "NJ", 40.298904, -74.521011},
            {"New Mexico", "NM", 34.840515, -106.248482},
            {"New York", "NY", 42.165726, -74.948051},
            {"North Carolina", "NC", 35.630066, -79.806419},
            {"North Dakota", "ND", 47.528912, -99.784012},
            {"Ohio", "OH", 40.388783, -82.764915},
            {"Oklahoma", "OK", 35.565342, -96.928917},
            {"Oregon", "OR", 44.572021, -122.070938},
            {"Pennsylvania", "PA", 40.590752, -77.209755},
            {"Rhode Island", "RI", 41.680893, -71.511780},
            {"South Carolina", "SC", 33.856892, -80.945007},
            {"South Dakota", "SD", 44.299782, -99.438828},
            {"Tennessee", "TN", 35.747845, -86.692345},
            {"Texas", "TX", 31.054487, -97.563461},
            {"Utah", "UT", 40.150032, -111.862434},
            {"Vermont", "VT", 44.045876, -72.710686},
            {"Virginia", "VA", 37.769337, -78.169968},
            {"Washington", "WA", 47.400902, -121.490494},
            {"West Virginia", "WV", 38.491226, -80.954456},
            {"Wisconsin", "WI", 44.268543, -89.616508},
            {"Wyoming", "WY", 42.755966, -107.302490}
        }
    ),

    AddClientID = Table.AddIndexColumn(Source, "Client ID", 1, 1, Int64.Type),
    AddClientName = Table.AddColumn(AddClientID, "Client Name", each [State] & " Corp"),
    AddDate = Table.AddColumn(AddClientName, "Date", each Date.FromText("2024-" & Text.PadStart(Text.From(Number.Mod([Client ID], 12) + 1), 2, "0") & "-" & Text.PadStart(Text.From(Number.Mod([Client ID], 28) + 1), 2, "0")), type date),
    AddSales = Table.AddColumn(AddDate, "Sales", each [Client ID] * 1000),
    AddSentimentScore = Table.AddColumn(AddSales, "Sentiment Score", each Number.RoundUp(Number.Mod([Client ID], 10) / 10, 2)),
    AddFeedbackSentiment = Table.AddColumn(AddSentimentScore, "Feedback Sentiment", each 
        let
            Sentiments = {"Positive", "Neutral", "Negative"}
        in
            Sentiments{Number.Mod([Client ID] - 1, 3)}
    ),
    AddFeedbackCloud = Table.AddColumn(AddFeedbackSentiment, "Feedback Cloud", each 
        let
            Feedbacks = {"great", "satisfactory", "poor", "excellent", "good", "average", "bad", "dry", "wet", "great staff"}
        in
            Feedbacks{Number.Mod([Client ID] - 1, 10)}
    )
in
    AddFeedbackCloud

Apply the Script

Click Done to close the Advanced Editor and apply the transformations.

Close & Apply

Once all columns are added and the script is applied, click on Close & Apply to load the transformed data into Power BI.

Step 2: Overview Page

Filter

Visualization > Slicer

Under "Field" - drag "Client Name," "State," and "Date" separately.

Under visuals > Slicer setting > drop down (to allow a drop down option).

Sales Page Button Tab

Gift Tractor

Card

DAX Calculation

Measure Tool > New Measure > Enter:


Average Sales per Sentiment = 
CALCULATE(
    AVERAGE('df'[Sales]),
    ALLEXCEPT('df', 'df'[Feedback Sentiment]))

Playing Around with DAX

Create YearMonth Column:

Go to Modeling > New Column:


YearMonth = FORMAT(Table1[Date], "YYYY-MM")

Line and Clustered Column Chart

Build Visuals:

Format Visuals:

Waterfall Chart

Category: Sentiment Score.

Y-axis: Sum of Sales.

Feedback Cloud

Insert > More Visuals > From AppSource > Search "Cloud" to find Word Cloud.

Click on Word Cloud icon under build visuals > Category: Drag "Feedback Cloud".

Step 3: Map Settings

Choropleth Map

Format Visuals:

Proportional Symbol Map

Donut Chart

Under Format Visualization (Visual Tab):

Adding the Smiley Face Image

Step 4: Sales Page

Add Moving Background

Adding Shapes Behind Graphs

Key Influencers

Decomposition Tree

Stacked Area Chart

Narrative