Grouping following Pivot operation

[palmerbi]

Hello. Im new to PowerBI but not to SQL so apologies if Im asking something silly. I just cant find solution  following searches.

I have got my data so far with pivot and the columns I want. In the below example I want to have the data grouped by SampleGUID on the one line. ie one line per GUID not 4. When I group in just SampleGuid I only see the one column. If I group on all its doesn help and I get what you see below. Any assistance greatly appreciated.

[erik_tarnvik]

Can you explain what your source data looks like or provide an example? I think I understand what you want the end result to be, but the rest is somewhat hard to understand.

[palmerbi]

Hi, will try and explain further.

My original data is like this.

InstrumentID	SampleGUID	Description	DateTime	Name	Value
2001-2006-05	C911A991-9055-4CFA-A8DF-8DAAFA59F303	Test1	28/08/2017 09:09:25	C/P Ratio	7.0994453408823
2001-2006-05	C911A991-9055-4CFA-A8DF-8DAAFA59F303	Test1	28/08/2017 09:09:25	Collagen	1.46323387092197
2001-2006-05	C911A991-9055-4CFA-A8DF-8DAAFA59F303	Test1	28/08/2017 09:09:25	Fat	6.03161719753644
2001-2006-05	C911A991-9055-4CFA-A8DF-8DAAFA59F303	Test1	28/08/2017 09:09:25	Protein	20.6105378753451
2001-2006-05	CB3D40F3-DB2D-4C96-953F-049FD7451E38	Test3	28/08/2017 09:12:11	C/P Ratio	5.16480318364629
2001-2006-05	CB3D40F3-DB2D-4C96-953F-049FD7451E38	Test3	28/08/2017 09:12:11	Collagen	1.04967512093616
2001-2006-05	CB3D40F3-DB2D-4C96-953F-049FD7451E38	Test3	28/08/2017 09:12:11	Fat	6.02534114597053
2001-2006-05	CB3D40F3-DB2D-4C96-953F-049FD7451E38	Test3	28/08/2017 09:12:11	Protein	20.3236228683375

For that I would be looking for 2 lines in transformed dataset.

InstrumentID	SampleGUID	Description	DateTime		c/p Ratio	Collagen	Fat	Protein
2001-2006-05	C911A991-9055-4CFA-A8DF-8DAAFA59F303	Test1	28/08/2017 09:09:25		7.0994453408823	1.46323387092197	6.031617	20.61054
2001-2006-05	CB3D40F3-DB2D-4C96-953F-049FD7451E38	Test3	28/08/2017 09:12:11		5.16480318364629	1.049675121	6.025341	20.32362

Ive got the columns where I want them but if you look at first page, Ive too many rows and I cant see what Im doing wrong.

Thank you.

[erik_tarnvik · Accepted solution]

I understood from your original question that you were using "Pivot Column". If you do you should actually get the result your are looking for, not sure what is going wrong.

In the query editor, select column "Name" , press "Pivot Column" and then select "Value" for the "Values Column" in the subsequent dialogue. This should result in the following which is what I beleive is what you wanted.

[palmerbi]

Thank you for your time. Not sure what was going on. I redid from scratch and work OK.  Might have been something non unique on the datetime on original data. Always good to get it working even if looking foolish!

Thank you