New row for every "On" state based on Binary 0 and 1

[Anonymous]

I have an issue of reading mutiple "On" states from binary on the same row, since i cannot make dax to show multiple result based on the same row data, see the below example of a typical row:

Unitnr.      Airpressure    Pressuretime   etc.    Status

1                    xx                     xxx                   1100000001000000000

2                    xx                     xxx                   1000000100000000000

3                    xx                     xxx                   1000100000000000000

Each of the 1 on the binary is a sensor with a name (i.e. 1, 2, 3 etc)

I would like to change it to:

Unitnr.      Airpressure    Pressuretime   etc.    Status                                         TLstatus

1                    xx                     xxx                   1100000001000000000                Sensor1

1                    xx                     xxx                   1100000001000000000                Sensor2
1                    xx                     xxx                   1100000001000000000                Sensor10

2                    xx                     xxx                   1100000001000000000                Sensor1

2                    xx                     xxx                   1100000001000000000                Sensor8

etc..............

I use TLStatus to  connect to a model in DAX which has description of what On-state of each sensor means.

Is this possible? Is there any other way, I have not thought of?

Thanks in advance

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

You can extract and expand these on rows on query editor side, below is steps and sample formula.

Steps:
1. Add a column to split all status character to list.
2. Transform above list to table.
3. Add index column to transformed table.
4. Filter table rows when status is '1'.
5. Extract index field list from above table.
6. Expand list to new rows to display all sensor index (status '1').

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaqoABMg0tDQAAJgtIGBUqxOtJIRmjJ0RVBlxpjKkNWAlcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Unitnr = _t, Airpressure = _t, Pressuretime = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unitnr", Int64.Type}, {"Airpressure", type text}, {"Pressuretime", type text}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TLstatus", each Table.SelectRows(Table.AddIndexColumn(Table.FromList(Text.ToList([Status])),"Index",1,1),each [Column1]="1")[Index]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "TLstatus")
in
    #"Expanded Custom"

Regards,
Xiaoxin Sheng