multiple columns if value=1 write column name in separate column

[Fryyy]

Hello,

i have a problem:

ich have multiple columns like that:

Station1       Station2      Station3     Station4

0                   1                 0                0

0                   1                 1                0

1                   0                 0                0

1                   1                 0                1

0                   1                 0                1

And if the value in a row is 1 i want to write the number (or name and number of the station) in a separate column like that

Station1       Station2      Station3     Station4                          Stations

0                   1                 0                0                                      Station2

0                   1                 1                0                                      Station2, Station3

1                   0                 0                0                                      Station1

1                   1                 0                1                                      Station1, Station2, Station4

0                   1                 0                1                                      Station2, Station4

Is it possible to do that? Maybe with a Measure or one new column in the query?

Would be great if you could help me.

Greetings

Fryyy

[Anonymous]

Hi I have a similar table - only I have a Customer ID column - how can I create the labels while maintaining which customer the rows are for?

[v-yulgu-msft]

Hi @Fryyy,

Based on my test, MarcelBeug's solution works great.

Alternatively, you can try another one, but it is not a direct way.

In Power Query editor mode, duplicate your source table first. And add an index column in both of them.

In 'Table1(2)', unpivot table structure like below.

After unpivoting columns, filter out those records where [value] is 0.

Save and apply all above changes, return back to data view mode. Create a calculated column in 'Table1(2)'.

Column =
CALCULATE (
    CONCATENATEX ( 'Table1 (2)', 'Table1 (2)'[Attribute], "," ),
    ALLEXCEPT ( 'Table1 (2)', 'Table1 (2)'[Index] )
)

In 'Table1', use a LOOKUPVALUE function to get the [Column] value from 'Table1(2)'.

Column =
LOOKUPVALUE ( 'Table1 (2)'[Column], 'Table1 (2)'[Index], Table1[Index] )

Best regards,

Yuliana Gu

[jthomson]

There's a few ways to look at this - if you don't actually need the fact that there's a value of one in these columns, you could just replace the values of 1 with the station name you want and zeroes with nulls in the query editor, or if you need them for something you could make a calculated column for each column based on the value (or, if you're uneasy with the query editor, use the DAX SWITCH command). Either way you can then just concatenate the columns together into one and get the result you want.

[MarcelBeug · Accepted solution]

Assuming you have also other columns, this would be a Power Query solution:

let
    Source = Table1,
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "Stations",
             (ThisRow) => 
                Text.Combine(
                    List.Transform(
                        List.Select(
                            List.Zip(
                                {Record.FieldNames(ThisRow),
                                 Record.FieldValues(ThisRow)}),
                            each _{1} = 1 and Text.StartsWith(_{0},"Station")),
                        each _{0}),
                    ", ")
            )
in
    #"Added Custom"