+ Reply to Thread
Results 1 to 7 of 7

Unpivot a terrible extraction from database

  1. #1
    Registered User
    Join Date
    07-11-2019
    Location
    London, England
    MS-Off Ver
    Windows 10
    Posts
    2

    Unpivot a terrible extraction from database

    We are working sometimes with Big Data and this is how our extraction from the database is set up, unfortunately. Is there a way to make it pivot-friendly?

    This is an example of sales of Brand A in Slovakia in Retail and Hospital segments.

    Each row is a subset, the total number is on top. thanks a looot!
    Attached Files Attached Files
    Last edited by DianaFraFra; 07-11-2019 at 07:00 AM.

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,659

    Re: Unpivot a terrible extraction from database

    1. Please update your profile to show your EXCEL version.

    2. I don't quite follow the rollup logic - Level 4 is not the sum of Level 5 (I'd expect row 5 values to be the same as row 6, or for there to be additional rows at the same level as row 6, to total the value in row 5) Is this a characteristic of your source data, that different grains contain different totals? Or is this just a characteristic of the reduced dataset you've provided as a sample?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,065

    Re: Unpivot a terrible extraction from database

    It would be far easier to sort the format when it's extracted. The logic for this output would be assuming and you would almost certainly get incorrect data output
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,169

    Re: Unpivot a terrible extraction from database

    Are the 9 row descriptors in A2:A10 repeated in A11:A19, A20:A28 ..etc

    It would be useful if you mocked up what you expect to see after you've added more rows and a translation has been performed for several records.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Registered User
    Join Date
    07-11-2019
    Location
    London, England
    MS-Off Ver
    Windows 10
    Posts
    2

    Re: Unpivot a terrible extraction from database

    Hi everyone, thanks for such a quick response. I updated the example. While making the actual data discrete I didn't notice that numbers didn't sum up.
    I want anything which is pivotable eventually, because right now I need to ask another department to load the raw data to PowerBI and then I can eventually extract it from PowerBI as a normal data table.

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,659

    Re: Unpivot a terrible extraction from database

    Okay. Will ALL data go down 5 levels of hierarchy?

    More pertinently, if Power BI can query the raw data, so can Excel - why not use the Power BI query?

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    5,659

    Re: Unpivot a terrible extraction from database

    I still firmly believe you'll get much better results by learning how to query against your source data directly, instead of unpicking a formatted export...

    And I remain concerned about your Excel version, given your profile doesn't state, and you've attached a .xls (97-2003 format) workbook.

    However... this Power Query does what you ask for. Format your source data as a table, first.

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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