Loading data to PowerBI stucked after applying changes to Power Query

[jinlei19906]

Hello Power BI Community,

I have built a Power Query which used Excel as its data source. I also did some data transformation such as merging queries, adding columns and changing types. It has been successfully applied in 5 minutes and I have built visualizations using that table. 

But later on I discovered that I do not need that many columns for the table. Considering the performance, there is a need to reduce the column number from some 500 to 37. But since visualizations and relationships are already built to the existing table, I tried to fix the existing query instead of building a new query. I tried the following steps but it takes much longer time than expected. After applying the change, the loading process changed to  "Creating connection in model..." and stayed there for more than 2 hours, so I cancelled the process. I then tried to remove 40 columns at one time and this was finished in 16 minutes. When removing 80 columns it took 30 minutes. Given this calculation, it will take around 3 hours to remove all the unnecessary columns. So I would like to ask if the following steps make sense for removing the columns and if there is any way to speed up this process.

Steps that I took to remove the columns in Power Query:

1. I first fixed the source Excel file, reducing the column number to 37.

2. Then I clicked "Edit Query" for the table, focused on the default generated "Changed Type" step and got this "column not found error" as expected. This is because these columns have been removed from the source.

3. Then I modified this step by removing the part that referencing the missing columns.

For example I changed the current query:

= Table.TransformColumnTypes(#"Promoted Headers",{{"ABC", type text}, {"ABD", Int64.Type}, {"ABE", type date}, {"ABF", type any}})

to:

= Table.TransformColumnTypes(#"Promoted Headers",{{"ABC", type text}})

in order to remove the extra column.

4. Then the error disappeared and I clicked "Apply and Close"

A loading window appears and when the status changes to "Creating connection in model...", it takes a long time to finish this step. 

Details of the data:

Originally it had 1665 rows, 500 columns, and 2MB. After removing the unnecessary columns, it has 1665 rows, 37 columns, 300KB. The table has 8 relationships with other tables.

Best regards,

Lei