Pivot table without aggregation in dataflow

Question

I am trying to pivot table without aggregation in dataflow.

From:

To:

Answer 1

@Petar Novakovic Hello and welcome to Microsoft Q&A.

I understand you want to do a pivot transformation in ADF data flow. Without an aggregation.

Unfortunately, it won't let us not choose an aggregation, so I've come up with next best options. These will still provide the desired output.

From what I can see of your data, DaTm values are unique, or at least the combination of Location and DaTm, or Location + DaTm + ID. Since there appears to be only one Attr value per cell, the aggregation doesn't really matter.

In Pivot Settings:

1. Group by Columns: Location and DaTm
2. Pivot Key: ID
3. Pivoted Columns: either first(Attr) or toString(collect(Attr))

Since there is only one Attr value, there is only one value for first to choose from, so it makes no difference. You could even use last if you wanted, it would make no difference.

If there was more than one Attr value, then toString(collect(Attr)) would get all of them and list them in the same cell.

For the pictures, the data I used was the first 8 rows of your data, with DaTm shortened for readability.

I believe this still accomplishes your desired output. Does this help?