cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Grouping following Pivot operation

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.

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

Re: Grouping following Pivot operation

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.

 

image.png

View solution in original post

4 REPLIES 4
Highlighted
Solution Specialist
Solution Specialist

Re: Grouping following Pivot operation

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.

Highlighted
Regular Visitor

Re: Grouping following Pivot operation

Hi, will try and explain further.

My original data is like this.

InstrumentIDSampleGUIDDescriptionDateTimeNameValue
2001-2006-05C911A991-9055-4CFA-A8DF-8DAAFA59F303Test128/08/2017 09:09:25C/P Ratio7.0994453408823
2001-2006-05C911A991-9055-4CFA-A8DF-8DAAFA59F303Test128/08/2017 09:09:25Collagen1.46323387092197
2001-2006-05C911A991-9055-4CFA-A8DF-8DAAFA59F303Test128/08/2017 09:09:25Fat6.03161719753644
2001-2006-05C911A991-9055-4CFA-A8DF-8DAAFA59F303Test128/08/2017 09:09:25Protein20.6105378753451
2001-2006-05CB3D40F3-DB2D-4C96-953F-049FD7451E38Test328/08/2017 09:12:11C/P Ratio5.16480318364629
2001-2006-05CB3D40F3-DB2D-4C96-953F-049FD7451E38Test328/08/2017 09:12:11Collagen1.04967512093616
2001-2006-05CB3D40F3-DB2D-4C96-953F-049FD7451E38Test328/08/2017 09:12:11Fat6.02534114597053
2001-2006-05CB3D40F3-DB2D-4C96-953F-049FD7451E38Test328/08/2017 09:12:11Protein20.3236228683375

 

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

 

InstrumentIDSampleGUIDDescriptionDateTime c/p RatioCollagenFat Protein
2001-2006-05C911A991-9055-4CFA-A8DF-8DAAFA59F303Test128/08/2017 09:09:25 7.09944534088231.463233870921976.03161720.61054
2001-2006-05CB3D40F3-DB2D-4C96-953F-049FD7451E38Test328/08/2017 09:12:11 5.164803183646291.0496751216.02534120.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.

 

 

Highlighted
Solution Specialist
Solution Specialist

Re: Grouping following Pivot operation

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.

 

image.png

View solution in original post

Highlighted
Regular Visitor

Re: Grouping following Pivot operation

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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors