Results 1 to 7 of 7

Thread: Unpivot some columns but retain others (variable data types)

  1. #1
    Neophyte ramakant's Avatar
    Join Date
    Mar 2020
    Posts
    4
    Articles
    0
    Excel Version
    office 365

    Unpivot some columns but retain others (variable data types)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi all,

    I'm a power BI newbie and trying to get a handle on unpivoting multi-header columns. I followed the tips mentioned on the well detailed page (https://www.newtechdojo.com/unpivot-...er-bi/#dualrow) . However, it slightly more complicated than the one shown on the page.

    Link to the data set on G Drive--> https://drive.google.com/open?id=1WO...VZLmKLiI0eA9Mx

    To summarise the problem statement. My current dataset sits something like this (there are around 25 markets)

    market01 market 02
    channel date title programme datatype1 ... datatype6 datatype1 ... datatype6

    the required output would need to look this:

    channel date title programe market datatype1 datatype2 datatype3 ... dataype6
    market01
    market01
    market02
    market02

    the normal unpivot solutions all are pointing in the direction where the 6 dataypes on the right hand side get merged into a single column. only problem with that approach is they are all different datatypes (numeric, time etc)

    I believe custom coding in M holds some magic to unlock this mystery. I have very little knowledge of M and am knocking on this forum's doors. How does one go about doing this?

    Edit: I must also mention that I am dealing with an extremely large excel sheets here. These are massive data dumps around 600MB of excel files each. Hence I am wary of not grinding my machine to a halt with excessive & undesirable unpivot queries. I tried it doing unpivotting one bunch of column at a time but the number of rows increasing exponentially. This is what leads me to believe that M coding might do the trick.

  2. #2
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    36
    Articles
    0
    Excel Version
    Office 365
    I think, my answer was a little bit to early !
    Last edited by pinarello; 2020-03-27 at 12:45 AM.

  3. #3
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    36
    Articles
    0
    Excel Version
    Office 365
    But now it looks good.

    Probably PQ experts will call it a kind of sledgehammer method, but the result should be right.

  4. #4
    Neophyte ramakant's Avatar
    Join Date
    Mar 2020
    Posts
    4
    Articles
    0
    Excel Version
    office 365

    Quote Originally Posted by pinarello View Post
    But now it looks good.

    Probably PQ experts will call it a kind of sledgehammer method, but the result should be right.
    dear pinarello,

    i have replicated the same and must say it works exactly the way it was intended to. A low skilled labourer like myself is satisfied with the sledgehammer approach

    may i ask you about the index and double indexed column. i was unable to understand how you sorted the first and second. Thanks so much for your time.

  5. #5
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,717
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi and welcome
    Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question

    Do not post any further responses in this thread until a link has been provided to these cross posts.
    Thank you Ken for this secure forum.

  6. #6
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    36
    Articles
    0
    Excel Version
    Office 365
    Hello ramakant,

    the first index would not have been necessary, because I only inserted it to check if after sorting, the records are in the correct order. A later sorting by channel and date would have done exactly the same.

    Decisive is the 2nd index. I use this index to determine the number of the market in the following step "Added Custom".

    As I am still learning myself, still very far away from a PQ expert, I also like to take care of requests that I understand intellectually. Because if I have developed a solution myself, then the chance is greater that I will also understand better solutions of others and thus learn again.

    In this respect I personally don't care how many forums a question is asked in. But nevertheless it makes sense to link the question in different forums with each other.

    But to avoid unnecessary multiple help, because even in one forum several helpers can take care of a question at the same time without knowing that others are already taking care of it, it would make sense that helpers could mark a question with an "I'll take care of it" tag, which would then be visible for everyone with date and time.

    Then every helper could immediately see whether one or more other helpers have already taken care of the request and decide for themselves whether they want to take care of it.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,750
    Articles
    0
    Excel Version
    365
    [Just practising on these older threads]
    A different approach at cell U3 of the expected output sheet in the attached.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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