Unpivot Function

[PN4]

Hi All,

I am connecting  a sharepoint list to Power Bi.  Also could export the data into excel to attempt the same,

Here is an example of what I'm trying to accomplish.  The colored columns corrospond to eachother and I want their data to remain connected. (I acually have 7 total columns I want connected but shortened it to three so I could demonstrate)

Current Raw Data:

Unpivot: (What I want the data to look like)

Am I making this harder than it really is?

[ImkeF · Accepted solution]

Hi @PN4 

1) Check columns "Site", "Manager" and "Date" -> Unpivot other columns

2) Check column "Attribute" and split column with " " as delimiter

3) Pivot back on the first column of the splitted columns with "Value" in the values area.

[PN4]

Thanks @ImkeF !

I am having issues with step 2 in splitting column "Attribute".

This this what the output is supposed to look like in splitting the column?

I'll also need some further explaination on how to perform step 3 and how to "Pivot back on the first column with "value"..."  Maybe that will become more clear to me after step 2 is complete.

Thanks again for your time in explaining this.

[ImkeF]

There's and underscore "_" in your column names. So you have to use that as the delimiter instead. Just the _, no quotes.

The pivot-back will be a pivot on column Attribute.1

[PN4]

@ImkeF 

This is awesome!  Thank you.  I'm still playing with this on the front side to see if I can get it to do what I had set up in Excel when I was manually unpivoting.  It appears to be adding up total records which is giving one too many, or when I add "attribute 2" it is missing the records without scores (which I need). 

You have solved my intitial problem though, in transforming the data, so thank you very much.

[Greg_Deckler]

I feel like this is something that @ImkeF  can help with.