Power query limit columns?

[jochendecraene]

Hi

I'm trying to load an excel with about 400 columns 😱 No ideal ...

Power query only loads up to about 200 colums. I found this on the microsoft power query limitations page

Number of columns per table

16,384

This is way more than 400. Any ideas what I'm doing wrong?

thnx!

[v-achippa]

Hi @jochendecraene,

Thank you for reaching out to Microsoft Fabric Community.

The issue you are describing might not be directly related to power query's hard limit for columns, as the limit for columns in a Power Query table is indeed much higher:

• If the file is stored on SharePoint, sometimes power query doesn't fully load large datasets due to connectivity issues. Try downloading the file locally to check if power bi can load all columns. If it works locally, the issue might be related to SharePoint performance or permissions.
• If there are underlying steps or query folding issues during the SharePoint load, power query might not load all columns. Check the Advanced Editor in power query and ensure there are no unnecessary transformations applied.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[SamWiseOwl]

Hi @jochendecraene 

Have you created any Applied Steps yet?

When connecting to the Excel sheet are you connecting to a Table, RangeName or Sheet.

If the Sheet option consider trying to turn the data into a table. Could you have an empty column?

[jochendecraene]

Hi @SamWiseOwl 

I'm loading the excel from a sharepoint folder, since there will be more files in the future.

I've made the data into a table in the excel file.

When loading in power query in the excel file from this table, I get all the columns.

When loading the same file in power bi, I only get about half of the colums. I didn't create any staps, except from the ones that are automatically created (see picture below)

[SamWiseOwl]

Could you try with just a couple of rows as a test.

I recreated it with some dummy data and again it works fine 

[jochendecraene]

Well, I created a test fill and indeed, it works just fine.

But the file of the client doesn't work in power bi, but it does in excel. It always stops at the same column. I checked the file. There are no blanc colums, or hidden columns ....

[SamWiseOwl]

Out of curiosity if you delete that specific column does it work?

Are there any privacy settings on the missing colums that are different from the initial 200?

[jochendecraene · Accepted solution]

Solved. It was an .xlsb, binary doc. 

[jochendecraene]

no, same result. Stops at exactly 255 colums ...