Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sab
Helper V
Helper V

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
v-yulgu-msft
Employee
Employee

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.

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

 

AkhilAshok
Solution Sage
Solution Sage

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.

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

Can you post a sample dataset which gives this error?

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

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/

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.