Forum Discussion
Solved
multiple columns if value=1 write column name in separate column
Hello, i have a problem: ich have multiple columns like that: Station1 Station2 Station3 Station4 0 1 0 0 0 1...
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"
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.
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"