Question

What’s the best approach to handle non-tabular data sources (e.g., unstructured data) in Power BI?

In my current Power BI project, I am dealing with non-tabular data sources, such as logs or unstructured text files. I need to process and transform these data types into a format suitable for analysis in Power BI. What are the best practices or tools within Power BI for handling non-tabular or unstructured data sources?

Answer 1

• In order to analyze data from non-tabular data sources in Power BI, a variety of mechanisms and methods should be applied to model the data accordingly. One of the most important ones is Power Query within Power BI, which facilitates ETL processes of untidy data. Most importantly, include the base data loaded regardless of its format, be it JSON, XML, or even simple text log files. In Power Query, there are also available features such as nested structure parsing and navigating, column cutting and joining, and tabulating ramps of shape data. As an outcome, one is able to apply their own specific M code in order to fetch and sanitize certain forms of data information automatically.

• When it comes to analyzing logs or other free-text documents, one has to rely on the so-called string functions in Power Query. These are such means as splitting of text by some Delimiter or not, regular expressions (possibly using other applications), and conditional columns as well, which can often be very useful in such scenarios. Most importantly, it is also very important that data types are clearly defined in the transformation stages, as this helps to eliminate loading difficulties during the actual uploading of the information to Power BI.

• Finally, in the case of large or complicated information sets, it is advised to shift the data processing to external tools, such as Python, R, or Azure Data Factory, that prepare the data with the aim of introducing it into Power BI. These enable the implementation of very heavy transformation or enrichment of the data, making it amenable to use. Once the data has been transformed and entered into Power BI, DAX functions can be applied for more advanced modeling and analysis purposes.