HOW TO DEVELOP SLOPE CHART IN POWER BI

5 min readFeb 2, 2024

Data visualization is an integral part of business intelligence, and Power BI stands out as a powerful tool in this domain. Slope charts are particularly effective in showcasing trends and changes between two points. While Power BI provides various visualization options, creating a slope chart might not be as straightforward. In this article, we will explore a Power Query trick to develop slope charts in Power BI, enabling users to present their data with clarity and precision.

Understanding Slope Charts:

Slope charts, also known as slope graphs or spaghetti charts, are ideal for illustrating changes in values between two points. They excel in representing trends, comparisons, and performance variations over time. Slope charts typically consist of lines connecting two data points, with the steepness of the line indicating the magnitude of change.

I have used EY Revenue Dataset range from 2009–2023 to do this work effectively.

EY Case study Introduction:

EY, formerly known as Ernst & Young, is one of the largest professional services firms in the world. It is a multinational firm that provides a range of services, including audit, tax, consulting, advisory, and transaction advisory services. EY is one of the “Big Four” accounting firms, along with Deloitte, PricewaterhouseCoopers (PwC), and KPMG. The firm serves clients across various industries and sectors, offering expertise in areas such as finance, technology, strategy, and more.

EY, or Ernst & Young, has various departments or service lines. The specific structure and names of departments may evolve over time, but generally, EY provides services through several core service lines:

Assurance: This involves auditing and assurance services to help clients meet their regulatory and reporting obligations.
Tax: EY provides tax advisory services to assist clients in managing their tax responsibilities and optimizing their tax positions.
Advisory: This encompasses a wide range of advisory services, including management consulting, risk management, technology consulting, and strategy consulting.
Transaction Advisory Services (TAS): This involves services related to mergers and acquisitions, transaction support, and corporate finance.
Consulting: EY offers consulting services in areas such as business transformation, technology implementation, and operational improvement.

Dataset:

Step-by-Step Guide:

Let’s dive into the step-by-step process of creating slope charts in Power BI using the Power Query trick:

Step 01 is preparing the data for referring the Fact tables that we can use for our analysis

let
    Source = Excel.Workbook(File.Contents("C:\Users\otto - admin\Desktop\EY Global Revenue from 2009 - 2023 (1).xlsx"), null, true),
    EY_Sheet = Source{[Item="EY",Kind="Sheet"]}[Data],
    // first step 
    #"Promoted Headers" = Table.PromoteHeaders(EY_Sheet, [PromoteAllScalars=true]),

    1://detect the data types of all columns of the entire table
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Assurance", type number}, {"Tax", type number}, {"Consulting", type number}, {"Strategy & Transactions", type number}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}}),

    2://removing null values from the table
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1", "Assurance", "Tax", "Consulting", "Strategy & Transactions"}),

    3://remoing values from rows in each column
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column1] <> null)),

    4://rename the Year Column
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}}),

    5://detecting data types agan in all columns
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"Assurance", type number}, {"Tax", type number}, {"Consulting", type number}, {"Strategy & Transactions", type number}}),

    6://Unpivot columns of departments 
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Year"}, "Attribute", "Value"),

    7://rename attribute column as Department and values as Revenue ($M)
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Department"}, {"Value", "Revenue($M)"}}),

    8://multiply revenue values by 1,000,000 to reflect a million unit values
    #"Multiplied Column" = Table.TransformColumns(#"Renamed Columns1", {{"Revenue($M)", each _ * 1000000, type number}}),

    9://change the data type to currency type $
    #"Changed Type2" = Table.TransformColumnTypes(#"Multiplied Column",{{"Revenue($M)", Currency.Type}})
in
    #"Changed Type2"

Step 02 ,creating Fact table 2 after reference from our source table as M coding shown above

let
    
    // this table referrenced from the below table
    Source = EY,

    //use Group by function to group  the minimum values which is year column (2009 &2023) to create Groupedmin table 
    GroupedMin = Table.Group(Source, {"Department"}, {{"Year", each List.Min([Year]), type nullable number}}),

     //use Group by function to group  the maximum values which is year column (2009 &2023) to create Groupedmax table 
    GroupedMax = Table.Group(Source, {"Department"}, {{"Year", each List.Max([Year]), type nullable number}}),

    // Union both tables using the table.combine functions 
    Union = Table.Combine({#"GroupedMin", #"GroupedMax"}),

    // merge this union tble with source table to get revenues values 
    #"Merged Queries" = Table.NestedJoin(Union, {"Department", "Year"}, EY, {"Department", "Year"}, "EY", JoinKind.LeftOuter),

    //expand the merged table to extract the revenue value column only
    #"Expanded EY" = Table.ExpandTableColumn(#"Merged Queries", "EY", {"Revenue($M)"}, {"Revenue($M)"}),

    // order columns sames as fact table 1 for easy appending processes
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded EY",{"Year", "Department", "Revenue($M)"}),

    //creating another depertmet column using custom column function
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "New Deps", each " " & [Department]),

    //detect the data type oth new created data type
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"New Deps", type text}})
in
    #"Changed Type"

Step 03, creating Fact Table after reference from our source table as M coding shown above

let
    Source = EY,

    // creating new column using custom column functions
    #"Added Custom" = Table.AddColumn(Source, "New Deps", each "Main-" & [Department]),

    //detecting data type of new column to text
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"New Deps", type text}}),

    //this is last step after finishing creating another fact table 2 which comprises min  and max values from our source table
    #"Appended Query" = Table.Combine({#"Changed Type", Fact_EY_2})
in
    #"Appended Query"