0

How can I pivot a table where there is more than one record for a given column heading? See images below - I have transformed data so that it's listed by date, and I want to pivot the table so that there is one column for each date, with the corresponding records. I have tried multiple ways using pivot column, group by column, etc. - but I can't seem to get what I need.

enter image description here enter image description here

Improve this question
1

That's a very weird requirement, but because this is Power Query then: yes we can. They key is to calculate a grouped running count which will drive the row numbers in your output. Following your sample data, Order 99292, 99391, 99397 etc would all get a running count = 0, then 99384, 99404 etc would get running count = 1.

Having derived the grouped running count, then you just need to Pivot on start_date, choosing Custom as your Values field and setting the Advanced / Aggregate Value option = Don't Aggregate.

There are a few different techniques around to calculate a grouped running count. I've used my favourite and put together a demo in my OneDrive folder:

https://1drv.ms/f/s!AGLFDsG7h6JPgw4

It's the file: Power Query demo - pivot column with multiple records

Improve this answer
2
  • Mike, I think I understand what you're saying - I'm not seeing that file in the link provided, but I will try to build a custom column following your logic. Thanks. – CBRF23 Apr 23 '19 at 12:52
  • Sorry, my OneDrive sync broke. The file should be visible now. Try List view and Sort by Modified. – Mike Honey Apr 23 '19 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.