Creating Connection in Model by loading from Excel

[xelan79]

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.

[v-stephen-msft]

Hi @xelan79 ,

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.

[xelan79]

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

[xelan79]

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.

[smpa01]

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

[xelan79]

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

[smpa01]

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

[watkinnc]

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

--Nate

[xelan79 · Accepted solution]

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.

[PedroPascual]

Anyone has a new solution for this topic??

[caveman_grug]

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.

[juan_pablo]

Great! this was the solution!

[Anonymous]

Thanks! That was it for me, phew! 😄