Unpivot some columns but retain others (variable data types)

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-columns-in-power-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=1WOI2NmWiPXGCeHs268VZLmKLiI0eA9Mx

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


the required output would need to look this:


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.

I think, my answer was a little bit to early !

But now it looks good.

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

pinarello said:

But now it looks good.

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

Click to expand...

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.

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.

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.

[Just practising on these older threads]
A different approach at cell U3 of the expected output sheet in the attached.