Issues with Expanding Columns in Power BI Dataflows When Importing JSON Files

[mh20221111]

When importing a JSON file in Power BI dataflows, sometimes the expand button does not appear in the upper left corner of the column, preventing expansion. This situation often occurs when the top of the data is blank and there is no list or record type data present.

If this situation arises, how can I expand the column? Is using the Advanced Editor for direct editing the only option?

[rohit1991 · Accepted solution]

Hi @mh20221111 ,
When importing JSON files into Power BI dataflows, the expand icon may not appear if the column doesn't contain recognizable list or record structures in the initial rows—often due to blank values or inconsistent formatting. In such cases, Power BI doesn't infer a structured type and therefore doesn't offer the expand option. 

A common workaround is to first filter out the null or blank rows so Power BI can detect the underlying structure, after which the expand button typically becomes available. If that doesn’t work, using the Advanced Editor is indeed a viable method—you can manually modify the M code to access and expand the nested records or lists. While it's not the only solution, it's often the most reliable in these edge cases.

[mh20221111]

Thank you for your response.
Before I asked my question, I tried filtering out the null or unstructured rows to ensure that the first visible row contained a record or list, but it didn't work at that time. However, after trying it again, the expand icon appeared.

[Akash_Varuna]

Hi @mh20221111 It might be because the top rows of the column are blank or do not contain list or record data, causing Power BI to misinterpret the column type. You can replace blank values with placeholders (such as {} or []) or bring a row with valid data to the top. You could try to, use the Advanced Editor to directly edit the M code for expansion. Adding a temporary dummy row with list or record data is another option.

[mh20221111]

Using dummy data is a good idea!

[v-pagayam-msft]

Hi @mh20221111 ,
Thank you for reaching out to us on  Microsoft Fabric Community Forum!

This issue usually occurs when the top rows in the JSON column are null or not structured as records or lists, which prevents Power Query from displaying the expand icon. As a work around , try filtering out the null or unstructured rows so that the first visible row contains a record or list, this may trigger the expand icon to appear. If that does not help, you may use the Advanced Editor to manually expand the column using M code like using below:       

          Table.ExpandRecordColumn(Source, "YourColumn", {"Field1", "Field2"}).

Hope this helps.If this answer resolve your issue,consider accepting it as solution.

Regards,
Pallavi.