Pivot table two lines should be one

I am working on a pivot table, the underlying database is tied to a common numeric identifier, and the data spans several years. While the numeric identifier is consistent, the text label associated ...

Book1.xlsx14 KB

BI & Data Analysis

excel

SergeiBaklan

Feb 01, 2022

LorneS
Creating PivotTable add data to data model and add calculated column

=
VAR agnt = Range[Agent]
RETURN
    CALCULATE (
        CONCATENATEX ( VALUES ( Range[Name] ), Range[Name], ", " ),
        ALL ( Range ),
        Range[Agent] = agnt
    )

Use it in PivotTable:

Book1 (14).xlsx133 KB

Lorenzo

Silver Contributor

Feb 01, 2022

Hi LorneS

With Get & Transform aka Power Query. See attached sample

LorneS

Copper Contributor

Feb 01, 2022

Thank you. One additional question - the query that you developed, was that written with the Advanced Editor or was there a menu selection and wizard that led to that step in the query?

Forum Discussion

Pivot table two lines should be one

Resources