cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Help with Pivoting needed

Hello,

 

I have a license log file, which among other columns, has Date and Status coulmns, see attached. 1.jpg

 

When I pivot these and I choose to not Aggregate I get the error: "Expression.Error: There were too many elements in the enumeration to complete the operation."

 

What to do in order to have two columns: IN and OUT having all respective dates in Rows?

 

Thank you!

Sabedin

8 REPLIES 8
Highlighted
Solution Sage
Solution Sage

Re: Help with Pivoting needed

How did you apply pivot? You have to first click on the Status column, select Transform->Pivot Column, give Values as Date and Aggregate as Don't Aggregate. This should ideally work.

Highlighted
Helper II
Helper II

Re: Help with Pivoting needed

Thanks for your reply. I know how to Pivot (just like your steps), but as I explained it returns an error. 

Highlighted
Solution Sage
Solution Sage

Re: Help with Pivoting needed

Can you post a sample dataset which gives this error?

Highlighted
Helper II
Helper II

Re: Help with Pivoting needed

Here:

 

https://sabedin-my.sharepoint.com/:x:/g/personal/sabedin_bidatalab_com/EQhzCrCwjaZMlJ312apfOtUBFUMiCdPH0HRrXqx0GP7xKw?e=i3B7J6

 

Let me know if you don't see the file

Highlighted
Super User IV
Super User IV

Re: Help with Pivoting needed

Hi,

 

There are multiple IN and OUT on each date.  For 10 Sep and 13 Sep, please show the exact result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper II
Helper II

Re: Help with Pivoting needed

I found a 'partial' solution here, I think you may want to know:

 

How to Pivot Column using Don't aggregate function with Power Query

 

 

Highlighted
Microsoft
Microsoft

Re: Help with Pivoting needed

Hi @Sab,

 

To avoid above error, you could add an index column first, then, pivot table. But, as Ashish_Mathur said, how to deal with the multiple records on each day, please show us your desired output.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Help with Pivoting needed

Using istructions from video above, I was able to create the ouput like this:

 

111.jpg

 

This is OK, but I lost information contained in other columns, therefore that solved my problem partially. 

 

Ideally I would like Out and In columns, along with other columns that I have (not provided in sample dataset).

 

Thanks

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors