Pointy Clicky Excel, Power Query, Fabric Workshop PART 1

Oct 31, 2024 Pre-SIMPLY CYBERCON #GRC Workshop

Author

Steve McMichael
October 28, 2024

How to Make a Cyber Risk Scorecard That Wins Hearts and Minds

Oct 31 Pre-SIMPLY CYBERCON #GRC Workshop

www.cpatocybersecurity.com/p/pre-simplycybercon2024

Agenda

Workshop Objectives

  1. Transform this CSF 2.0 Organizational Profiles Spreadsheet at NIST.gov…

…into:

  • A questionnaire template, for determining top 5 cyber risks at an organization

  • The template includes:

    • 25 questions for the business, grounded in NIST CSF guidance (categories, subcategories and implementation examples)

  • A 0-10 scoring legend

  • A Cybersecurity Framework profile

    • By function

  • And by category

  1. Get hands on with Fabric, the open source framework for augmenting humans with AI

Follow Along!

Step 1: Download the CSF 2.0 Organizational Profiles Spreadsheet

NIST is Using Excel Tables. Why Should We?

Overview of Excel tables - Microsoft Support

To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel

table.support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

Instead of using explicit cell references

Excel uses table and column names

=Sum(C2:C7)

=SUM(tblCSF[Actual Score])

To make a table: 

cmd+t (or ctrl+t in Windows)

Step 2: Rename the table

💡 Tables are inputs to queries, so it’s helpful to label them with “tbl” as foundational elements to reference with queries later 

tblCSFtemplate

Step 3: Add a “CSF Outcome Type” Column

Not Sure Which Formula to Use? Try asking Chat GPT 

=IF(LEN(A2)=2, "Function", IF(LEN(A2)=5, "Category", "Subcategory"))

  • Save your workbook

Step 4: Extract, Transform, Load (ETL) with Power Query

  • Extract: Power Query connects to multiple data sources, allowing you to pull data from databases, web services, files, and more.

  • Transform: After extraction, Power Query lets you clean and shape the data to fit your analysis needs. You can filter, rename columns, merge tables, remove duplicates, and apply other transformations, making the data structured and usable.

  • Load: Finally, the transformed data can be loaded directly into Excel or Power BI for further analysis and visualization, with the added benefit of being refreshable whenever new data is available.

  • Data > Get Data (Power Query)

  • Excel workbook

  • Select tblCSFtemplate

  • Filter by CSF Outcome Type

#"Filtered rows" = Table.SelectRows(#"Changed column type", each ([CSF Outcome Type] = "Category")),

  • Modify and remove columns

#"Renamed columns" = Table.RenameColumns(#"Filtered rows",

{"Included in Profile?", "Actual Score"}, {"Rationale", "Minimum Target"}, {"Current Priority", "Gap to Minimum"}, {"Current Status", "Desired Target"}, {"Current Policies, Processes, and Procedures", "Gap to Desired Target"}),

#"Removed columns" = Table.RemoveColumns(#"Renamed columns", {"Current Internal Practices", "Current Roles and Responsibilities", "Current Selected Informative References", "Current Artifacts and Evidence", "Target Priority", "Target CSF Tier", "Target Policies, Processes, and Procedures", "Target Internal Practices", "Target Roles and Responsibilities", "Target Selected Informative References", "Notes", "Considerations"}),

  • Set score columns to decimal number

#"Changed column type 1" = Table.TransformColumnTypes(#"Removed columns", ),

  • Rename columns

#"Renamed columns 1" = Table.RenameColumns(#"Changed column type 1",

"CSF Outcome (Function, Category, or Subcategory)", "Category"),

  • Duplicate the CSF Outcome column

#"Duplicated column" = Table.DuplicateColumn(#"Renamed columns 1", "Category", "Category - Copy"),

  • Split it

#"Split column by delimiter" = Table.SplitColumn(#"Duplicated column", "Category - Copy", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv), {"Category - Copy.1", "Category - Copy.2"}),

  • Final clean up:

#"Removed columns 1" = Table.RemoveColumns(#"Split column by delimiter", {"Category - Copy.2"}),

#"Renamed columns 2" = Table.RenameColumns(#"Removed columns 1", ),

#"Reordered columns" = Table.ReorderColumns(#"Renamed columns 2", {"Function", "Category", "CSF Outcome Type", "CSF Outcome Description", "Actual Score", "Minimum Target", "Gap to Minimum", "Desired Target", "Gap to Desired Target"}),

#"Removed columns 2" = Table.RemoveColumns(#"Reordered columns", {"CSF Outcome Type"}),

  • Add scores of 5 to Minimum Target

#"Replaced value" = Table.ReplaceValue(#"Removed columns 2", null, 5, Replacer.ReplaceValue, {"Minimum Target"})

in

#"Replaced value"

Your Power Query Script

let
  Source = Excel.Workbook(File.Contents("/Users/steve/Documents/ACCRP_Camtasia/CH12_SpreadsheetLabCharts/CSF 2.0 Organizational Profile Template Draft.xlsx"), null, true),
  #"Navigation 1" = Source{[Item = "tblCSFtemplate", Kind = "Table"]}[Data],
  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"CSF Outcome (Function, Category, or Subcategory)", type text}, {"CSF Outcome Type", type text}, {"CSF Outcome Description", type text}}),
  #"Filtered rows" = Table.SelectRows(#"Changed column type", each ([CSF Outcome Type] = "Category")),
  #"Renamed columns" = Table.RenameColumns(#"Filtered rows", {{"Included in Profile?", "Actual Score"}, {"Rationale", "Minimum Target"}, {"Current Priority", "Gap to Minimum"}, {"Current Status", "Desired Target"}, {"Current Policies, Processes, and Procedures", "Gap to Desired Target"}}),
  #"Removed columns" = Table.RemoveColumns(#"Renamed columns", {"Current Internal Practices", "Current Roles and Responsibilities", "Current Selected Informative References", "Current Artifacts and Evidence", "Target Priority", "Target CSF Tier", "Target Policies, Processes, and Procedures", "Target Internal Practices", "Target Roles and Responsibilities", "Target Selected Informative References", "Notes", "Considerations"}),
  #"Changed column type 1" = Table.TransformColumnTypes(#"Removed columns", {{"Desired Target", type number}, {"Gap to Minimum", type number}, {"Minimum Target", type number}, {"Actual Score", type number}, {"Gap to Desired Target", type number}}),
  #"Renamed columns 1" = Table.RenameColumns(#"Changed column type 1", {{"CSF Outcome (Function, Category, or Subcategory)", "Category"}}),
  #"Duplicated column" = Table.DuplicateColumn(#"Renamed columns 1", "Category", "Category - Copy"),
  #"Split column by delimiter" = Table.SplitColumn(#"Duplicated column", "Category - Copy", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv), {"Category - Copy.1", "Category - Copy.2"}),
  #"Removed columns 1" = Table.RemoveColumns(#"Split column by delimiter", {"Category - Copy.2"}),
  #"Renamed columns 2" = Table.RenameColumns(#"Removed columns 1", {{"Category - Copy.1", "Function"}}),
  #"Reordered columns" = Table.ReorderColumns(#"Renamed columns 2", {"Function", "Category", "CSF Outcome Type", "CSF Outcome Description", "Actual Score", "Minimum Target", "Gap to Minimum", "Desired Target", "Gap to Desired Target"}),
  #"Removed columns 2" = Table.RemoveColumns(#"Reordered columns", {"CSF Outcome Type"}),
  #"Replaced value" = Table.ReplaceValue(#"Removed columns 2", null, 5, Replacer.ReplaceValue, {"Minimum Target"})
in
  #"Replaced value"

  • Name your query 

qryCSFtemplate

  • Close & load

Step 5: Lift and Shift Your Query Output to a New Table

  • Copy paste values to new a tab tblCRMAPresults

Step 6: Customize Your Questionnaire Template

  • Add a Question column, then drag left

  • Add 5 more rows for when there are multiple questions per CSF category:

    • GV.SC[a], [b], [c]

    • PR.AA[a], [b]

  • Set a number format and decimals

Step 7: Add and XLOOKUP to the Mastering Cyber Resilience Questions

Step 8: Group Columns for Questionnaire

Step 9: Add Scoring Legend and Data Validation

Step 10: Add Data Validation

0-1.9 Rarely or never
2.0-4.9 Sometimes, but unreliably.  Rework is common
5.0-5.9 Consistently, with minor, occasional flaws
6.1-7.9 Consistently, with great effectiveness and high quality
8.1-10.0 At excessive financial cost.  People can't easily get their work done

Step 11: Add Slicers for Quick Filtering

On to PART 2

Pointy Clicky Excel, Power Query, Fabric Workshop PART 2

Oct 31, 2024 Pre-SIMPLY CYBERCON #GRC Workshop

www.cpatocybersecurity.com/p/excel2