Forum Discussion
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 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
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"
4 Replies
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"
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
