Controls Matrix using Slicers

[Peak Seagull]

1. Add or overwrite the data in Table 1 on sheet Sheet1 ensuring:

• the data in the Identifier column is TEXT (your 5.10 is different from 5.1!)
• in each cell where there are #s, make sure there's a space before a # character in any 2nd or subsequent #word to separate it from the previous #word (a space is used to split the data within a cell)
• remove any hyphens (some are invisible! They're soft hyphens (ascii code 173))
• that the table includes all the new data (drag grab handle in bottom right of table to adjust the size of the table if it's not automatically in the right place

2. It is a pivot table based on a query on the data in sheet Sheet1
3. Sheet2 is the result of a query on the data in Sheet1 but is only used in the solution at cell J1 of Sheet1. Some rows are hidden because of the slicer choices on Sheet1

Attached is a workbook containing only the necessary for Table2 tab.
If you update the data on Sheet1 then be sure to Refresh the pivot table on Table2 tab.
To see what's going on in the background:

View attachment 73882
The pivot table is based on that data.

Hi again - I've tried to replicate your Power Query in a different workbook but

Yes. AKA Get & Transform data. It's built in now.
See:
Install Power Query Excel 2013 | MyExcelOnline

Install Power Query Excel 2013 | MyExcelOnline

Power Query in Excel can be used to access, clean and transform all that messy data and displays it in a way that Excel can work with. Click here to learn more about Power Query Excel 2013!

www.myexcelonline.com

How To Install Excel 2010 Power Query | MyExcelOnline

How To Install Excel 2010 Power Query | MyExcelOnline

Power Query in Excel can be used to access, clean and transform all that messy data and displays it in a way that Excel can work with.

www.myexcelonline.com

Thank you for the links - I have tried to replicate your Power Query in a separate workbook as I wanted to change 2 of the headings but it didn't work (probably because I don't know what I'm doing). The identifier column should be called ISO/IEC 27002 control identifier and the name column should be called Control name - are you able to change these for me?

 

[p45cal]

I'm not 100% certain of your headers. The attached is a guess.
If it's wrong then attach a file with the new headers and included queries and I'll edit.

 

[Peak Seagull]

Thank you for this it's incredibly helpful.

I've attached a spreadsheet showing the correct headers (which I think you'd done correctly anyway) and I've just repositioned the tables on each tab. Is it possible to create filter slicers like you've done for the Security domains column for the other columns? Please feel free to say no as I don't want to take advantage. I also think I better do a Power Query course - are there any free ones on You Tube that you'd recommend?

 

[p45cal]

I'm not sure if the attached is what you want. The idea is to select the Category in the first slicer then choose the #Word from the second slicer.
These slicers can be renamed

 

[Peak Seagull]

I'm not sure if the attached is what you want. The idea is to select the Category in the first slicer then choose the #Word from the second slicer.
These slicers can be renamed

Hi - that is so clever - thank you ever so much for your help - if you do know of any free you tube courses that explain how to do this that would be great. Thanks again

 

[Peak Seagull]

I'm not sure if the attached is what you want. The idea is to select the Category in the first slicer then choose the #Word from the second slicer.
These slicers can be renamed

Is it possible to rename the tab Table2 to something else?

 

[p45cal]

Is it possible to rename the tab Table2 to something else?

Of course! It can be practically anything. Why don't you try it?

I'm not a fan of learning from videos, well for big subjects anyway. It's OK to show off some small detail, but for anything more you're forever pausing, going back, replaying. I prefer text, be it in a book or on screen. I teach myself mostly.
One book I used was https://www.excelguru.ca/content.php?293-M-is-for-(Data)-Monkey
The ExcelGuru.ca website is owned by Ken Puls who is also the author of the book. The website is full of resources, many free.
The co-author of the book, Miguel Escobar, has a lot of on-line stuff, (some of them videos!):

but also features in some of the blogs at ExcelGuru.
Of course, Microsoft do some too (this is an old one): https://support.microsoft.com/en-us...e1f-bad5-89f6269cd605?ui=en-us&rs=en-us&ad=us
Power Query M formula language reference - PowerQuery M | Microsoft Docs

Power Query M formula language reference - PowerQuery M

Power Query M is used to filter, combine, and mash-up data from one or more supported data sources.

docs.microsoft.com

Power Query - Overview and Learning - Excel

About Power Query in Excel - Microsoft Support

With Power Query, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.

support.microsoft.com

Understanding Power Query M functions - PowerQuery M | Microsoft Docs

Understanding Power Query M functions - PowerQuery M

Learn more about: Understanding Power Query M functions

docs.microsoft.com

Getting Started with Power Query – Part I | Microsoft Power BI Blog | Microsoft Power BI

Getting Started with Power Query – Part I

Welcome the Excel and Power BI getting started series. Today we’re starting with the first step of the data analysis process: Discover and Get Data. Power Query allows you to easily discover and connect to data from public and corporate data sources…

powerbi.microsoft.com

Chris Webb as well:
Chris Webb's BI Blog: Conditional logic in Power Query Chris Webb's BI Blog

Chris Webb's BI Blog: Conditional logic in Power Query

Writing a simple if statement in Power Query’s M expression language is straightforward. Using an Excel table called Input that contains a single value as the starting point: The following query sh…

blog.crossjoin.co.uk

Some others:
Introduction - Power Query

Introduction - Power Query

The Power Query M formula language is optimized for building highly flexible data mashup queries. It's a functional, case sensitive language similar to F#. Power Query Formula Language is used in a number of Microsoft products such as Power BI Desktop, Excel, and Analysis Services.

www.powerquery.io

Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6 - Simple Talk

Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6

In this article of the Power BI series, Robert Sheldon demonstrates how to work with the Power Query M language to import and transform data.

www.red-gate.com

Advanced transformations on multiple columns at once in Power BI and Power Query – The BIccountant

Advanced transformations on multiple columns at once in Power BI and Power Query –

Learn how to use custom complex functions bulk-wise on your tables: Advanced transformations on multiple columns at once in Power BI and Power Query.

www.thebiccountant.com

Learning Power Query (Get & Transform) | My Spreadsheet Lab

Learning Power Query (Get & Transform) | My Spreadsheet Lab

This year I got serious about learning Power Query! Also known as Get & Transform. Read my post to follow my journey.

www.myspreadsheetlab.com

Power Query - Custom Functions - Excel Off The Grid

How to use Power Query Custom Functions

In this post, we explore the topic of Power Query custom functions - What are they, When should we use them, And how to create them

exceloffthegrid.com

 

[Peak Seagull]

Of course! It can be practically anything. Why don't you try it?

I'm not a fan of learning from videos, well for big subjects anyway. It's OK to show off some small detail, but for anything more you're forever pausing, going back, replaying. I prefer text, be it in a book or on screen. I teach myself mostly.
One book I used was https://www.excelguru.ca/content.php?293-M-is-for-(Data)-Monkey
The ExcelGuru.ca website is owned by Ken Puls who is also the author of the book. The website is full of resources, many free.
The co-author of the book, Miguel Escobar, has a lot of on-line stuff, (some of them videos!):

but also features in some of the blogs at ExcelGuru.
Of course, Microsoft do some too (this is an old one): https://support.microsoft.com/en-us...e1f-bad5-89f6269cd605?ui=en-us&rs=en-us&ad=us
Power Query M formula language reference - PowerQuery M | Microsoft Docs

Power Query M formula language reference - PowerQuery M

Power Query M is used to filter, combine, and mash-up data from one or more supported data sources.

docs.microsoft.com

Power Query - Overview and Learning - Excel

About Power Query in Excel - Microsoft Support

With Power Query, you can search for data sources, make connections, and then shape that data (for example remove a column, change a data type, or merge tables) in ways that meet your needs. Once you’ve shaped your data, you can share your findings or use your query to create reports.

support.microsoft.com

Understanding Power Query M functions - PowerQuery M | Microsoft Docs

Understanding Power Query M functions - PowerQuery M

Learn more about: Understanding Power Query M functions

docs.microsoft.com

Getting Started with Power Query – Part I | Microsoft Power BI Blog | Microsoft Power BI

Getting Started with Power Query – Part I

Welcome the Excel and Power BI getting started series. Today we’re starting with the first step of the data analysis process: Discover and Get Data. Power Query allows you to easily discover and connect to data from public and corporate data sources…

powerbi.microsoft.com

Chris Webb as well:
Chris Webb's BI Blog: Conditional logic in Power Query Chris Webb's BI Blog

Chris Webb's BI Blog: Conditional logic in Power Query

Writing a simple if statement in Power Query’s M expression language is straightforward. Using an Excel table called Input that contains a single value as the starting point: The following query sh…

blog.crossjoin.co.uk

Some others:
Introduction - Power Query

Introduction - Power Query

The Power Query M formula language is optimized for building highly flexible data mashup queries. It's a functional, case sensitive language similar to F#. Power Query Formula Language is used in a number of Microsoft products such as Power BI Desktop, Excel, and Analysis Services.

www.powerquery.io

Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6 - Simple Talk

Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6

In this article of the Power BI series, Robert Sheldon demonstrates how to work with the Power Query M language to import and transform data.

www.red-gate.com

Advanced transformations on multiple columns at once in Power BI and Power Query – The BIccountant

Advanced transformations on multiple columns at once in Power BI and Power Query –

Learn how to use custom complex functions bulk-wise on your tables: Advanced transformations on multiple columns at once in Power BI and Power Query.

www.thebiccountant.com

Learning Power Query (Get & Transform) | My Spreadsheet Lab

Learning Power Query (Get & Transform) | My Spreadsheet Lab

This year I got serious about learning Power Query! Also known as Get & Transform. Read my post to follow my journey.

www.myspreadsheetlab.com

Power Query - Custom Functions - Excel Off The Grid

How to use Power Query Custom Functions

In this post, we explore the topic of Power Query custom functions - What are they, When should we use them, And how to create them

exceloffthegrid.com

That's great - thanks for all the information I'll have a look through. I like to learn by sitting with someone who knows what they are doing rather than videos and books but that's obviously not an option at the moment. You've been ever so helpful - thanks a lot.

PS I didn't want to rename the tab in case it messed anything up with linking etc.