Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating Connection in Model by loading from Excel

Dear Community,
I have a problem with a query with load data from Excel. The data cannot be loaded because by applying the changes PowerBI gets stuck with the message "Creating Connection in Modell". I have already turned off the "Data Load" options (Background data, Parallel loadings, Time intelligence and Autodetect relationsships.

I have also installed the new November Update 2021, because I read this in similar issues in this forum. But this does not help.
The data has more than 3000 columns. Is it possible, that this is too big for PowerBI?

My memory has 16GB and I work on a Amazon Workspace.

What I further do not understand: After Unpivoting the dataset the columns can be changed in to rows. Then everything  is fine and I do not get this problem during applying. However this is no solution, because I need the column view to add more calcucated columns (and remove columns I do not need).

Thanks for your help in advance. Please let me know, if I forgot important information about my enviromment.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi all,

    thanks for your advice. After starting a new project and reducing the columns in the first step, everything is fine. I created a "main" table for the data load and defined all other queries as reference in this.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I'm going to guess that yes, 3000 columns might not be ideal for a tabular model.

     

    --Nate

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi all,

      thanks for your advice. After starting a new project and reducing the columns in the first step, everything is fine. I created a "main" table for the data load and defined all other queries as reference in this.

      • caveman_grug's avatar
        caveman_grug
        New Member
        4 years ago

        There is also a simple error that can cause BI to hang at this stage. In your excel file, ensure all columns to the right of your data have been highlighted and deleted even if they appear blank. Excel may be reading all columns as part of the worksheet and BI doesn't like it. This worked for me after trying several other posted solutions regarding changing Options, clearing caches etc.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Column limit - The maximum number of columns allowed in a dataset, across all tables in the dataset, is 16,000 columns. This limit applies to the Power BI service and to datasets used in Power BI Desktop. 

     

    Check whether the data you import is greater than 1GB, about optimizing the data source, you can refer to

    Data reduction techniques for Import modeling - Power BI | Microsoft Docs

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for this information. I have started building up a new projekt and now it works better. It was some issue with too many dataloads

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  what i sthe source of the data;  meaning how are you getting the excel?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      The data is collected from several companies (espiacially insurances) The data is deliverd per company and for reporting year

       

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        Anonymous can you get them from a db? If yes, you can do the transformation server side and only bring what you need

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Additional Information:
    When I load the table and pick the sheet and do basic operations (Remove Colums, or Rows) everything is fine. After adding "Use First Row as Header" applying does not work anymore.