If you are aware about Power BI Data sources, then you must be knowing SQL Server as one of the most generic data sources. In SQL server if all the values are directly present under each of the fields, then it is super easy to create a dataset from SQL Server tables. But it would be tricky if the data is available in XML format in the database column. If you see the below example, the 2nd field “XMLData”, is storing xml values in the database table. This data can not be used directly in Power BI as a Dataset to create the Report.
SQL database can be connected to Power BI in 2 different ways :
- Import
- Direct Query
Import Option imports selected tables into Power BI Desktop.Then Power BI uses the columns from these selected tables for Data Visualization.
Direct Query option is used to create Visualization from current/live data.In this case, data is not imported in Power BI, while an interaction is created between Visualization and database tables.
Below Steps can be used to use xml data, using Direct Query Option :
- Go to File tab and Click on Get Data Option. Select SQL Server Database as the data source :
2. In SQL server Database config window provide Server name and database name. Choose Direct Query Option. Provide the query to be used to extract the data from SQL database table. Click on OK :
3. In next window, provide database Username and Password, to which database has to be connected and Click on Connect Button :
4. Query with database columns will be available on the right side of the window, as a dataset. Right Click on dataset and Click on Edit Query Option :
5. In Power Query Editor window, Use Parse Option from Transform Tab, to Parse xml data and expand the Table after parsing xml data column. There are multiple transformation options available under Transform Tab, which can be used to apply on parsed xml data, as per the requirement :
6. Once all the transformations are done, Click on Close & Apply Option from the Home Tab. All the fields from Parsed XML will be available under the dataset on the right side. You can Rename the dataset name also :
Now, all the fields from new dataset can be used to create data visualization, as shown below :
Once this Report is published in Power BI Service portal, as we are using direct query mode, data needs to be refreshed in the portal as well, when new data arrives. To update the latest data, schedule refresh must be created in the Service portal.
This is how xml data from the database table can be visualized in Power BI Report.