Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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"

13.png

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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"

13.png

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.