Hi again - I've tried to replicate your Power Query in a different workbook but1. Add or overwrite the data in Table 1 on sheet Sheet1 ensuring:
2. It is a pivot table based on a query on the data in sheet Sheet1
- 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
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.
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?Yes. AKA Get & Transform data. It's built in now.
Install Power Query Excel 2013 | MyExcelOnline
How To Install Excel 2010 Power Query | MyExcelOnlinePower 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
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. Read step by step on Excel 2010 power querywww.myexcelonline.com