Oops I formatted my data in a really unhelpful way - can you help me compensate?

I have a really wide table (~90 columns). I've just realised that I've formed the table in a way that doesn't best aid in developing pivot tables and pivot charts (I have power pivot). Here is a simplified version of my table

Capture.JPG

but I can't find out how to get 'level' into a slicer (or similar). With hindsight, what I should have done is construct my table in this form:

Capture2.JPG

Is there a way I can use 'level' as a slicer in my existing table form? I've found sets and they seem useful (for example I can make a set called 70000 that contains r70000 and a70000), but I can't figure out how to use this new found knowledge in a dashboard where I can push a button to switch between different levels. Is anyone able to help me with this please?

Many thanks

Hello,

I'd go one step further and normalise into type (r or a) . You can easily do that with Power Query. If you have Power Pivot, you also have Power Query.

Load the data into Power Query, select the first three columns (width, length, depth), on the Transform ribbon select "Unpivot other columns". Split the column with the text "r70000" etc into two, after the first character. Then rename the columns.

Here is the M code that was recorded when I performed these steps.

Please Login or Register  to view this content.

2018-01-04_UnpivotResult.png

For future reference, please take a moment to re-read forum rule #1 and apply the rules below when next opening a thread here. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

Please mark this thread as solved using the thread tools option at the top.