• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Controls Matrix using Slicers

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
How To Install Excel 2010 Power Query | MyExcelOnline
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?
 
Last edited:

p45cal

Well-Known Member
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.
 

Attachments

Last edited:
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?
 

Attachments

p45cal

Well-Known Member
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
 

Attachments

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
 

p45cal

Well-Known Member
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/office/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605?ui=en-us&rs=en-us&ad=us
Power Query M formula language reference - PowerQuery M | Microsoft Docs
Power Query - Overview and Learning - Excel
Understanding Power Query M functions - PowerQuery M | Microsoft Docs
Getting Started with Power Query – Part I | Microsoft Power BI Blog | Microsoft Power BI

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

Some others:
Introduction - Power Query
Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6 - Simple Talk
Advanced transformations on multiple columns at once in Power BI and Power Query – The BIccountant
Learning Power Query (Get & Transform) | My Spreadsheet Lab
Power Query - Custom Functions - Excel Off The Grid
 
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/office/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605?ui=en-us&rs=en-us&ad=us
Power Query M formula language reference - PowerQuery M | Microsoft Docs
Power Query - Overview and Learning - Excel
Understanding Power Query M functions - PowerQuery M | Microsoft Docs
Getting Started with Power Query – Part I | Microsoft Power BI Blog | Microsoft Power BI

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

Some others:
Introduction - Power Query
Power BI Introduction: Power Query M Formula Language in Power BI Desktop — Part 6 - Simple Talk
Advanced transformations on multiple columns at once in Power BI and Power Query – The BIccountant
Learning Power Query (Get & Transform) | My Spreadsheet Lab
Power Query - Custom Functions - Excel Off The Grid
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.
 
Top