Forum Discussion
Import from Excel with Data Type columns doesnt matter
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:
- 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.
- 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.
- 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
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
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
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.
