Design Strategy for IoT data

[samgreene1]

I have data that looks like the below table.  These are AC/Lighting data.  I have numerical and text data mixed together in the Value column. About 100mm rows in our table today and long term will get up to 2.4 Billion or more as we bring more buildings online. In the case of blower-2 we have a text status and also some numerical values.  Note that there are a huge number of device/measure combinations and I don't have a listing of which are numeric or textual. 

How would you handle this using Power BI with the data in a SQL table?  Would you preprocess into a star schema using T-SQL or an ETL tool?  Would the fact table have a numerical number and also a 'textual' field with a FK to a DIM_DeviceStatus?  Would this work well once things are inside power bi?  I could also consider moving out of SQL into another data store in Azure if that will work better.  

Most examples online have either a numeric OR a text fact that is converted to an FK, not finding case studies on this scenario.  

Timestamp	Building	Device	MeasureName	Value (varchar)
1/1/2022 12:00:00	Building 1	Space-1	Temperature	74.2
1/1/2022 12:00:00	Building 1	Space-2	Temperature	72.1
1/1/2022 12:00:00	Building 2	Blower-2	Status	Off
1/1/2022 12:00:00	Building 2	Blower-2	Status	1

Thanks for you advice. 

[samgreene1]

Thanks for your reply.  Yes, I am aware that this is text and won't be able to be used as a measure - which is really the root of my issue and the reason I ask the question with guidance on how to separate the numeric and textual data.  The data is stored in sql server and making a connection is not an issue.   

[v-yiruan-msft · Accepted solution]

Hi @samgreene1 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. 

1. Copy the table in Power Query Editor

2. Add a custom column to get the numeric value and text value sperately in these two tables

= Text.Select([#"Value (varchar)"],{"0".."9","."})

= Text.Select([#"Value (varchar)"],{"a".."z","A".."Z"})

3. Filter the value of custom column is not null

4. Remove value column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJSMDSyMjAAokMLDi1Q0lFyKs3MScnMS1cwhAoEFyQmp+oCeQpQgZDU3ILUosSS0qJUsKACWNTcRM8Iwo7VQTcdxVi4kUaoZoHMMNIzJKAdpMkpJ788tUjXCOK8EqDuYiDDPy2NBL3IOoF2xgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Building = _t, Device = _t, MeasureName = _t, #"Value (varchar)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type datetime}, {"Building", type text}, {"Device", type text}, {"MeasureName", type text}, {"Value (varchar)", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Col_Number", each Text.Select([#"Value (varchar)"],{"0".."9","."})),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Col_Number] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value (varchar)"})
in
    #"Removed Columns"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[v-yiruan-msft]

Hi @samgreene1 ,

First of all, it should be clear that the column containing both text values and numeric values will only be recognized as Text type when loaded into Power BI. Also, a column can only have one data type, it is not possible to switch between multiple data types in one column. Is your data source SQL? If so, you can refer to the following document to connect to SQL in Power BI Desktop.

Connect to SQL Server database from Power Query Desktop

How to Connect Power BI to a SQL Server Database

Also, considering your data volume is relatively large, we suggest you refer to the following link to design and optimize your data model first.

Design a data model in Power BI

Power BI - Data Modeling

Optimizing the data model

Best Regards