Pivot - grouping the fields

hi all.

  1. Below is the raw data file

  1. I am using Parabola pivot to move CRSD dates into new columns.

a) As you see on the screenshot below, the first PN, 38950-00901-101, is shown as two separate rows with values under 12/14/2020 and 12/15/2020 columns. Shouldn’t this be shown in a single row?

b) Assuming I can fix question a), can I then group these new columns by month like in Excel? I have already converted the format to date in an earlier step.

Thank you for your help

Hey Malik!

The Pivot step will “collapse” those rows into a single row if every value in the other columns that are not specified in the pivot step settings are equal across the rows. If it ends up with the format that you are seeing, it means that collapsing the rows would have lost data.

For example, in your second screenshot, looking at the two top rows, the Part $, Manager, and WCSD are all the same, but the Order Date is not the same across the rows. That is preventing it from representing those two rows as a single row.

Either you need to remove columns that are causing rows to be unique, or you need to approach this differently.

Taking the data from your initial screenshot, can you manually create in a spreadsheet the desired result? I may be able to walk you backwards from that.

Thank you for your help @brian

Regarding my question a), you are right, I missed that.

Can you help with question b), see my 2nd screenshot in my original message. If there are 4 new pivot columns with Dec 14, Dec 15, Dec 21, Dec 28, can I group those just as “December”, like I would do with an Excel pivot?

You can, if you use a Date formatter to change the date (or make a new column) that just expresses the month that the date falls in.

Then you could use any of the grouping steps. If you search the sidebar by “Group” you will see things like Avg Values, Sum Values, etc. Choose which operation you want to use, and then you can group that result by certain columns.

Thank you @brian for your quick help. I will continue working on the flow and let you know if I can’t solve any other step.

1 Like