Forum Discussion

jochendecraene's avatar
jochendecraene
Helper V
1 year ago
Solved

Power query limit columns?

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!

Interesting Links
Need Help
Show and Tell

8 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    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's avatar
      jochendecraene
      Helper V
      1 year ago

      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's avatar
        SamWiseOwl
        Super User
        1 year ago

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

         

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

         

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    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