"Power Query" from PBI Desktop: Data import fixed at 255 columns only

[amirbenchouai]

Hello, I need help importing data from Excel Binary files into power Bi desktop. In fact, when I import the files, the number of columns is set to 255 whereas there are many other columns but they do not appear. Can you help me with this please?

[rubayatyasmin]

Hi, @amirbenchouai 

The issue you're experiencing might be due to certain limitations in Power Query. In general, Power Query should be able to handle a lot more than 255 columns, but there might be specific limitations or quirks with certain data sources or file types.

Here are a few steps you could follow to attempt to resolve this issue:

1. Verify Excel File Format: Firstly, please ensure your Excel Binary file (.xlsb) doesn't have any issues itself. For instance, can you open the file in Excel and see all the columns? If the file itself is problematic, then Power BI will naturally struggle with it.

2. Import File as Text: If the file seems fine in Excel, you might want to try importing it as a text file instead. Power BI might be interpreting the file format incorrectly or there might be an incompatibility with the .xlsb format. Here's how:

• Save your Excel Binary workbook as a .txt or .csv file.
• In Power BI, click "Get Data" -> "Text/CSV", then locate the .txt or .csv file.
• See if this allows you to see all columns.

3. Use a Different Data Connector: Another potential workaround is to use a different data connector. Instead of using the Excel connector, you could try the OLE DB connector or ODBC connector.

4. Update Power BI Desktop: Ensure you're using the latest version of Power BI Desktop. Microsoft periodically releases updates that fix bugs and expand functionality, so it's always a good idea to make sure you're using the latest version.

5. Use Excel's Power Query: If all else fails, you might want to try using Power Query in Excel first to import the data and then export it in a format that Power BI can handle.

[amirbenchouai]

Hey @rubayatyasmin, 

Thanks a lot for your response, here bellow my answers:

1. Verify Excel File Format: no errors on the files, and I can open them in excel and see all the columns
2. Import File as Text: I can’t switch to CSV or Txt files, the files are large and I work with others teams with the same files
3. Use a Different Data Connector: I tried but I’m stuck at the connection step. In fact, I work in local connection “C://…”. It is possible de do it with ODBC connector.
4. Update Power BI Desktop: I’m using the last version of PBI Desktop the July 2023 version.
5. Use Excel's Power Query: Same problem using excel, it’s stuck in the 255th column.

[rubayatyasmin]

@amirbenchouai 

can you split your data into chunks to see if the problem solves? or can you please check if the Excel file you are working on has any hidden formatting such as hidden data, rows, or columns? May that is causing the problem. Or try to load the data in different platforms to see if the problem is in PBI only or any other platform as well. 

[amirbenchouai]

Hey @rubayatyasmin,

no hidden data in the files in question. unfortunately, i work only with PBI & Excel.