Help with Pivoting needed

[Sab]

Hello,

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

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

[v-yulgu-msft]

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

[Sab]

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

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]

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.

[Sab]

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

[AkhilAshok]

Can you post a sample dataset which gives this error?

[Sab]

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

[Ashish_Mathur]

Hi,

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

[Sab]

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