Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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:
For Detailed Information:
Create a custom data type in Power Query
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
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.
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.
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:
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:
Kees Stolker
A big fan of Power Query and Excel
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.
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:
For Detailed Information:
Create a custom data type in Power Query
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