SQL endpoint to Fabric LakeHouse not returning complex datatype in Power Query Editor

[SqlBobScot]

We have a Fabric DataLake some of the columns are DataType: complex. When we connect to the Fabric DataLake using the SQL endpoint, the fields which are  DataType: complex, are NOT returned in Power BI Query editor.  No error message this rest of the columns load as expected, only the columns which are  DataType: complex are not returned.

[SqlBobScot]

Thanks for the reply, from what I can see it looks like I have not communicated the issue clearly.  Allow me to describe our issue in different words.

In the Fabric LakeHouse we have a table with 30 columns, 2 of those columns are "DataType: complex". We connect using a DataFlow, to the Fabric LakeHouse using a SQL Endpoint. In the DataFlow we can see

28 columns

The 2 columns in the Fabric LakeHouse which are "DataType: complex", do not appear in the DataFlow.

I'll check to see if this is a known bug.

[v-xinruzhu-msft]

Hi @SqlBobScot 

Thanks for your quick reply, as i mentioned, the complex type is not supported in power query editor, so it may cannot appear, you can refer to the link i offered.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SqlBobScot]

Thanks for the reply, if the column is not in the Dataflow, how can I use the techniques that are suggested in the post you provided?  To use those techniques, the column requires be returned in the dataflow and so accessible to the Power Query Editor so that I can use the M-code / techniques suggested.

Thank you for providing the link the article unfortunatey it does not address the issue we are facing ☹️

[v-xinruzhu-msft]

Hi @SqlBobScot 

Based on your description, the complex data type is not a support data type in power query, you can refer to the following link about the supported data types in power query.

M Language types - PowerQuery M | Microsoft Learn

It is better that you can  transform the complex data types into a format that the power query supports before importing the data to Power query.

Or you can refer to the following link about using function to transform the complex type.

Custom functions and complex return types in Power Query – Gerhard Brueckl on BI & Data (gbrueckl.at...

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.