Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |