Import from Excel with Data Type columns doesnt matter

[Prodingero]

I have in excel Power query produced in Excel File "Verkauf" a table "Umsatz" with a column "Person", which is a own created Datatype described in Einen Datentyp erstellen (Power Query) - Microsoft-Support.
If I try to import File "Verkauf" in Power BI Desktop with Power Query the table "Umsatz", Power Query typed the column "Person" as '123 Text'.  I Can´t extract the column "Person", allways it appears "Error". In Power BI Power Query, Power Query doesn´t insight, tha Person includes a field type List. Otherwise the import from Sharepoint Lists with columns from type Lists does matter.
I have asked Copilot, but the answer is, the ie no direct way to extract filds with createtd datatype from Excel.
Is that really so?

[v-pagayam-msft]

Hi @Prodingero ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.

[v-pagayam-msft]

Hi @Prodingero 
Thank you @PwerQueryKees  and @Nasif_Azam  for the insightful responses!

As PwerQueryKees  and Nasif_Azam said, correct .Power BI doesn’t support custom data types (like lists or records) created in Excel Power Query. These types are Excel specific and appear as text or cause errors when imported into Power BI. To avoid this, expand or flatten the column  in Excel Power Query before loading the file into Power BI.

Hope this helps.If so,give us kudos and consider accepting it as solution.
Thank you.

Regards,
Pallavi G.

[PwerQueryKees]

My German is not too good, but I saw in the artikel you are linking to that it explicitly states that datatypes are only supported in Excel. Not sure what that means though. Maybe loading a PQ datatype can only be done in Excel.

But, if I understand your question correctly:

• You loaded some data using PowerQuery for Excel.
• You created a custom data type in PowerQuery (like in the video in the article you link to)
• You loaded the resulting table to you excel workheet
• You then load this Excel file in Power BI and you get errors.

If I understand correctly, this would immediately mean that Power BI does not understand the data type you created in Excel.

Have you tried opeing the result data in excel using PQ for Excel?

If so, have you tried copying the the Power Query M Code from Excel into Power BI?

If you need further help, please share:

• an example of your data,
• the powerquery you used in Excel to define the datatype,
• powerquery you use in Excel to read back the data with the datatype
• the powerquery you use to import the excel in Power BI.

Kees Stolker

A big fan of Power Query and Excel

[Prodingero]

In Excel Power Query it is the same problem. So I am waitng for a solution like Excel files can read from power query as odata, like readung SharePoint Lists. 

[Nasif_Azam · Accepted solution]

Hey @Prodingero ,

It seems like the issue arises because Power BI Power Query is unable to directly recognize or handle custom data types created in Excel (like the “Person” column with a List data type). Power BI does not yet support extracting or directly manipulating custom data types created in Excel when importing the file. Few possible workarounds:

1. Convert the List to a Recognized Type: Before importing the file into Power BI, try to expand the “Person” column in Excel to a simpler type (e.g., a text or number column). You can do this by selecting the List within Excel (using Power Query) and expanding it into individual fields or values. This way, Power BI can import it more easily.
2. Pre-process the Data in Power Query: If you can’t modify the Excel file, you can load the file into Power BI, and then in Power Query, try to expand the “Person” column manually by using the “Expand” option or transforming the data into a simpler format that Power BI can handle.
3. Use a SharePoint List Instead: Since SharePoint lists work better with List-type columns, if possible, you could consider migrating this data to a SharePoint list where the List type is more natively supported.

For Detailed Information:

Create a custom data type in Power Query

How to fix Power Query errors

Power BI SharePoint List Integration

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam