Power Query Pivot

Power Query PivotFollow
Dorin Danci
Dorin DanciMar 27, 2023 01:57 PM
Hi, I would like to transform table data from duplicate rows (multiple PARIDs) to one row (per PARID): …
1. Power Query Pivot

Dorin Danci
Posted Mar 27, 2023 01:57 PM | view attached
Hi,

I would like to transform table data from duplicate rows (multiple PARIDs) to one row (per PARID):

Original data:

PARID ATTRIBUTE VALUE Adj $ Adj %
01001111 SPLMSSI 1136 0 3
01001111 LBFSI 1 9 -5
01001111 EYSI 1989 6 0
Final output:

PARID SPLMSSI VALUE Adj $ Adj % LBFSI VALUE Adj $ Adj % EYSI VALUE Adj $ Adj %
01001111 SPLMSSI 1136 0 3 LBFSI 1 9 -5 EYSI 1989 6 0
I would like to use Power Query to do the transformations.

See attached file for more details.

Please let me know if you have any questions.

Your help is really appreciated.

Thank you.

——————————
Dorin Danci

——————————
Attachment(s)

xlsxPower Query Pivot test.xlsx

2. RE: Power Query Pivot

Bronze Contributor
James Watts
Posted Mar 28, 2023 04:14 AM
Will your data always have sets of three lines to be rearranged in the same way ?

——————————
James Watts
Business Intelligence Analyst
——————————

3. RE: Power Query Pivot

Dorin Danci
Posted Mar 28, 2023 01:27 PM
Hi James,

The number of rows with similar PARID can vary from 1 to more than 10.

As a result, the number of resulting columns will have to match the number of “duplicate” rows.

The Power Query PIVOT function does the trick if I start with only 3 columns:

PARID ATTRIBUTE VALUE
– The names in the ‘ATTRIBUTE’ column are used to create new columns

– The data from the ‘VALUES’ column become new values in the new columns

Thank you for looking into it.

Dorin

——————————
Dorin Danci
BC Assessment
——————————