Pivot multiple value

[Mars3442]

Hi everyone,

I want to ask for help solving my problem

I have a table like this

I need to group the vehicle based on customer and period

My expectation table like this

What should I do if I want to get my expectation table?

Please help me to solve this problem

Thank you..

[v-kaiyue-msft · Accepted solution]

Hi @Mars3442 ,

Please follow these steps:

1.Create a calculation table and write the following expression.

Table 2 =

var _table1=

DISTINCT('Table'[Customer])

return

ADDCOLUMNS(

    _table1,

    "M01",

    CONCATENATEX(

        FILTER(ALL('Table'),

       'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Period]=1),'Table'[Vehicle],","),

    "M02",

      CONCATENATEX(

        FILTER(ALL('Table'),

       'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Period]=2),'Table'[Vehicle],",")

   

)

2.The result obtained is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[slorin · Accepted solution]

Hi,

1. Transform "Period" 1, 2, ... --> M01, M02, ...

2. Pivot with Text.Combine

let
Source = Your_Source,
Period = Table.TransformColumns(Source, {{"Period", each "M" & Number.ToText(_, "00"), type text}}),
Pivot = Table.Pivot(Period, List.Distinct(Period[Period]), "Period", "Vehicle", each Text.Combine(_, ", "))
in
Pivot

Stéphane

[v-kaiyue-msft · Accepted solution]

Hi @Mars3442 ,

Please follow these steps:

1.Create a calculation table and write the following expression.

Table 2 =

var _table1=

DISTINCT('Table'[Customer])

return

ADDCOLUMNS(

    _table1,

    "M01",

    CONCATENATEX(

        FILTER(ALL('Table'),

       'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Period]=1),'Table'[Vehicle],","),

    "M02",

      CONCATENATEX(

        FILTER(ALL('Table'),

       'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Period]=2),'Table'[Vehicle],",")

   

)

2.The result obtained is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.