topic Re: Creating Connection in Model by loading from Excel in Power Query

Creating Connection in Model by loading from Excel

Anonymous — Tue, 23 Nov 2021 15:45:05 GMT

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.

Re: Creating Connection in Model by loading from Excel

Anonymous — Tue, 23 Nov 2021 17:52:39 GMT

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

--Nate

Re: Creating Connection in Model by loading from Excel

smpa01 — Tue, 23 Nov 2021 18:11:48 GMT

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

Re: Creating Connection in Model by loading from Excel

Anonymous — Tue, 23 Nov 2021 22:43:03 GMT

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

Re: Creating Connection in Model by loading from Excel

smpa01 — Tue, 23 Nov 2021 22:48:00 GMT

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

Re: Creating Connection in Model by loading from Excel

Anonymous — Wed, 24 Nov 2021 11:53:44 GMT

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.

Re: Creating Connection in Model by loading from Excel

Anonymous — Fri, 26 Nov 2021 05:56:32 GMT

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.

Re: Creating Connection in Model by loading from Excel

Anonymous — Fri, 26 Nov 2021 11:05:45 GMT

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

Re: Creating Connection in Model by loading from Excel

Anonymous — Sat, 27 Nov 2021 19:51:38 GMT

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.

Re: Creating Connection in Model by loading from Excel

caveman_grug — Wed, 20 Apr 2022 23:56:37 GMT

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.

Re: Creating Connection in Model by loading from Excel

Anonymous — Thu, 12 May 2022 09:22:36 GMT

Thanks! That was it for me, phew! 😄

Re: Creating Connection in Model by loading from Excel

PedroPascual — Fri, 21 Jul 2023 10:18:21 GMT

Anyone has a new solution for this topic??

Re: Creating Connection in Model by loading from Excel

juan_pablo — Thu, 09 Nov 2023 21:13:15 GMT

Great! this was the solution!