Business Intelligence for developers
Power BI uses Power Query (M) to extract and transform the data that is loaded into the data model.  A Power Query statement is broken into individual steps that are reevaluated each time the report dataset is refreshed.  This fluidity can be leveraged to return dynamically structured datasets.  Power Query can consume unstructured data, such as JSON or XML, and programmatically expand each element into separate columns as part of its sequence of applied steps.

Once the Power BI report is published, each time it is refreshed, it will retrieve the current unstructured base data from the source JSON column.  Then, it will parse &amp; update the column structure in the resulting dataset automatically, without needing to manually refresh the data &amp; republish using Power BI Desktop.

If the source data is not already in an unstructured format, you will need to prepare the raw tabular data for Power Query ingestion.  The desired columns will need to be rendered into key/pairs in a valid JSON string in the source table that you will reference in Power Query Editor.  Make sure you put these columns into a single JSON object for each row, as opposed to putting the key/pair values for every row into nested objects within a single element.  SQL Server 2016 and above have some JSON formatting tools that can assist with this transformation.

The required Power Query functions are as follows:


Table.TransformColumns to return the contents of a valid JSON string into a Json.Document object.  This applied step can be easily inserted by selecting "Transform -> JSON" from the context menu of the column header in Power Query Editor.

JSONTableName = Table.TransformColumns(sourceTableName,{{JSONStringColumnName, Json.Document}})


Table.ExpandRecordColumn to parse the JSON object key into columns.  In order to have the columns dynamically rendered you will also need to use a combination of Record.FieldNames, Record.Combine, &amp; Table.Column.

DynamicTableName = Table.ExpandRecordColumn(JSONTableName, JSONStringColumnName, Record.FieldNames(Record.Combine(Table.Column(JSONTableName, JSONStringColumnName))))


One consideration for this dynamic approach, if you remove a key/value pair from the JSON object, it will, by design, remove the column from the refreshed dataset.  Even though any visualizations that are using that particular column will then show an error, it is not a critical issue and can automatically be fixed by clicking the "Fix This" button.  However, to avoid this altogether, it is best to avoid removing columns from the structure.