Weird huge size loading

[h_l]

There are 2 data sets,

Set1 is a single excel table, contains about 7000 rows 3 columns, size 164KB. 3 columns. ALL are text.

Set2 is getting data from local disk folder, 19 excel files, 141MB in total. Each file contains about 180K rows, 8 columns.

Contains Date, URL, Keywords, Impressions, Clicks data.

Trim, lowercase are applied to column URL, then a custom column is added, to search if a any value in [URL] contains any value in another column in Set1 and returns the contained text.

The M is:

=let myvalue=[URL]
in
Text.Combine(
Table.SelectRows(Keywords,
each Text.Contains(myvalue,[Terms]))[Terms]
,
",")

But after I click transform and load, the Power BI loading it for even a hour and displaying, and it is still loading with increasing size

The file -deduplicated.xlsx is the one only has 164KB size.

Anyone could help to advise the reason and how to resolve this?

Thanks in advance.

[manjusajjan]

Any solution for this data loading issue? I am facing similar problem

[japss]

did you find the issue behind this huge loading? any suggestion to resolve? I have same issue that is loading overnight for several gb

[h_l]

Not really😅

[V-lianl-msft]

This may be a problem with your M code.Please check the referenced columns and M code, and you can also refer to this blog to reduce waste of memory:

https://us.hso.com/blog/power-query-for-excel-load-settings-minimize-wasted-memory/ 

[h_l]

Emm, but I didn't see problem in M, anyway, thanks for reply.

[HarishKM]

@h_l   Hey Mate ,

You can upload any Excel workbook smaller than 1 GB to Power BI. An Excel workbook can have two parts: a Data Model, and the rest of the report—the core worksheet contents. If the report meets the following size limits, you can save it to OneDrive for Business, connect to it from Power BI, and view it in Excel Online:

• The workbook as a whole can be up to 1 GB.
• The core worksheet contents can be up to 30 MB.

Reduce the size and limitation of excel 

Use it and test your case .

[h_l]

@HarishKMThanks for reply, but the problem is I only have either only 164KB in excel 1, the rest 19 excel files, each is less than 10MB. Not the file size issue. I think the key is I have no idea why it has loading for several hours and seems without ending...

If I remove the M in Power Query, everything will be loaded well.

Just after adding the M, the loading seems no ending

[HarishKM]

@h_l  Try to load a folder then check or load a random indiviual file for testing and see .

[h_l]

I assume it is because some specific value is exising in URL or Keyword, such as:

/b80de788-69b7-4104-b9e0-e08620201476

When it is included, PowerBI cannot process it even us a crossjoin , testing now.

Thanks

[h_l]

After a night, it goes to 41 GB...

Any idea please?