Slow data refresh

[CloudMonkey]

Hi,

Normally my power bi file takes about 5 minutes to refresh, but now it it taking at least 30 minutes (and counting). I noticed that the refresh screen is saying that it has (currently) read several million rows from my source tables which is strange because the source files only have a couple of hundred thousand rows. Does anyone have any idea what the problem may be?

Thanks,

CM

[Anonymous]

Any chance you are referencing that table for us in other tables?  I definitely see behavior like this and am the middle of investigating it.  My thought was that the merge was being done each time the table was used which is not what I was expecting when using the initial table as a reference.

[Gazzer]

Is it possible that you have created a full join between two tables? 

[CloudMonkey]

Hi @Gazzer I merged a query and expanded one column (i.e. I effectively did a vlookup from one table into another to populate one column). But I don't see why that would cause 20 million rows (and counting) to be read for a table that is only about 115,000 rows?

[Gazzer]

What was the join type? 

Left outer?

Full outer?

Something else?

[CloudMonkey]

I would attach a screenshot of the merge but if I click on insert image I don't see a browse diaglogue to select the screenshot file.

Anyway it is a left out join referring to the same table.

The table has 3 relevent fields:

Contract number

Timestamp when contract was created

Related contract number (used if a contract relates to an earlier contract)

I'm then using the merge to create a field to show the timestamp of the "Related contract number" (where one is present)

The table has about 115,000 rows and currently the "apply query changes" dialogue box is showing 258 millions rows and counting!

Thanks,

CM

[Gazzer]

The fields with the IDs being used for the join - are there multiples of each value in each field, or are they unique in one of the tables? It sounds like you have a many-to-many relationship.