Design question - storing array of values in each row of a table

[churchjm]

I'm working on a report to explore a scientific dataset, and I'm wondering if it's possible to store an array of values (e.g., an earthquake waveform time series) in each row of a table. I know I could create a fact table where each row represents a single sample from a seismometer, but this would result in massive duplication of the foreign keys (i.e., earthquake_id, station_id, instrument_id, etc.). That is, a single 60 second waveform recorded at 100 Hz would turn into 6,000 rows in this scheme, each with identical foreign keys except for timestamp. It would be much more compact to store each waveform in a single row comprising a starting timestamp, the seismometer sampling rate, and the array of samples, as all information necessary to plot each waveform could be derived from these three columns. Is such a design possible in Power BI? I realize my scientific use case falls outside the realm of BI, and that I may need a more general tool like d3.js, but I was hoping I could implement this in Power BI so I could add an interesting report to my portfolio. I'd appreciate any advice you could offer!

[churchjm]

@DataNinja777 Thank you for your thorough response! I'll investigate the Python/preprocessing options as I should be able to accomplish most of my goals with one of these approaches.

[DataNinja777 · Accepted solution]

Hi @churchjm ,

Power BI does not support storing arrays or complex objects like waveform time series directly within a single table cell in a way that allows for native parsing, manipulation, or charting. The data model in Power BI is based on VertiPaq, which is optimized for flat, columnar storage. Each cell is expected to hold scalar values such as numbers, dates, or text—not lists or arrays.

To represent a time series like a waveform sampled at high frequency, the most compatible design with Power BI would be to store each sample as a separate row, along with its corresponding metadata and timestamp. However, this can quickly become large and inefficient. For example, a 60-second waveform sampled at 100 Hz would generate 6,000 rows per station-event combination, leading to significant duplication of metadata columns and potentially large file sizes.

An alternative is to store the waveform data as a delimited text string or a JSON blob in a single column. While Power BI can store such strings, it cannot parse them natively for plotting or analysis. This limits what you can do with the data inside Power BI unless you use external scripts. Python and R visuals in Power BI can be used to parse and visualize the waveform from the delimited string, but these visuals are not ideal for performance or scalability and often require the user to enable special permissions.

Another option is to preprocess the waveform data externally—using Python, R, or other tools—to generate a visual representation such as a chart or image. That image can then be stored as a URL or as base64-encoded content and rendered in Power BI using custom visuals. This approach is more efficient when the waveform does not need to be dynamically sliced or filtered, and it allows you to focus on the metadata analysis and presentation within Power BI.

While Power BI is not designed for scientific use cases involving high-frequency time series, it can still be used effectively for reporting if the data is pre-processed into a form that aligns with Power BI’s strengths. For fully interactive waveform exploration and dynamic rendering, a tool like D3.js or a scientific computing platform would be more appropriate. However, using Power BI to present and summarize metadata, with embedded waveform previews, can still make for an informative and visually engaging report.

Best regards,