+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    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
    Last edited by jlt199; 01-05-2018 at 12:00 PM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Win 2010-2016
    Posts
    11,190

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

    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

  3. #3
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

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

    Thank you, this was really helpful

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    17,870

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

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1