Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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"
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?
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
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"
User | Count |
---|---|
73 | |
70 | |
38 | |
25 | |
23 |
User | Count |
---|---|
96 | |
93 | |
50 | |
43 | |
42 |