MS Excel / VBA help thread

look for "grow table" in this thread to find the post

Cheers. Let me have a look.

No it's not actually billions. I can't remember how many rows because it keeps crashing. But it's lots.

I've done 482 rows and then after that it just dies.

I'm having to use the Web app just to look at it. Even though it can't do anything, at least it doesn't crash.

482 rows by a milion and 12 columns?

open a new excel book, pq and import the two tables but only load as connections. that should help.

You know what. You've made me pause and think. There must be something wrong here to have this many rows.

is it a table that's been generated by a merge?
I'm guessing there's some duplication going on?

7,652 rows

Well. It seems that bitching about it online has worked.

I've managed to copy the formula in the Web app. It's only been 4hrs of trying on and off inbetween other tasks/calls.

I set formulas not to calculate until I close. So now I have the thrill of closing it and reopening.

That's not that long, how wide is the data sheet?

Anyway, you've made progress!

^not wide enough that it warrented mugging me off for two days. I'm wondering if they've reduced my RAM on the sly as the other day it said I didn't have enough memory for something.

a table that's been generated by a merge

No. It's a report from two sheet that I combined, made 2 UIDs, filtered to get my list of issues and pasted that list to a new sheet, then looked up against the old sheet.

Why not just use PQ to delete the excess columns from the og sheet? Well yes in hindsight I should have done that. But I need to put it in a template which uses different header names.

With PQ Merg function, why could I only Merg into one sheet/the original source data rather than whichever one I chose?

As chalfie said, sounds like you want to use a merge in PowerQuery.

But even doing it using an xlookup in standard Excel should only take a few minutes. My NUC which has a 5 year old mobile processor in it did that easily for a million rows looking up against a million rows.

With PQ Merg function, why could I only Merg into one sheet/the original source data rather than whichever one I chose?

Did you Merge as New? If so you should be able to choose whichever from the drop downs.

1 Attachment

Going round in circles with PQ.

When I try and load i get

[dataformaterror] we couldn't convert to Number

How do I identify the problem area?

Basically I've deleted a load of columns and added some new concat columns, but I swear to god I've been through all of those to check they're packed full of Text.From

I was hoping to load at this point because the sheet should be more manageable and I can't get my if lookup to return a result in PQ.

It says there is an error in A537 but my version is locked to display a certain amount of KB so I can't get more rows.

Are you trying to concatenate a number and a text column in PQ?

Have you selected based preview on the entire data set rather than top 1000 rows that might help you load more and if you can catenate a number in a text then you have to treat as text I think

do? I want to apply for a job at Manchester City football club?

Yes. I need a few number text concatenations. But I thought Text.From(xxx) would deal with it.

Now I have a sort of grasp of what I'm doing I'm going to start again.

Being able to tidy up and remove the unnecessary columns without xls crashing has been a real game changer.

And I'm hoping by copying that detailed view thing I can copy and save that to re-run all these early steps.

Unfortunately where I really struggle is with more detailed formulas - e.g. I want to look for a matching UID against the whole colum of UID where the value of another column = "hamster" then when there is >1 result concat those with a ; delimiter. In excel I can just about work it out, but 0

Yes.

I'll see if I can find that option.

I swear my current version of PQ looks more stripped down than it used to. I have also lost the PQ tab like this:

And only have it as option within data (I think).

There have been efficiency drives so I wonder if they've scaled back the office package or something. Likewise, I could be paranoid but I think they've reduced the number or processors(?) or something on my VM. My ram is the same but I'm routinely maxing out my machine using xls.

I don't have a powerpivot or power query tab - but this isn't a big thing I use the data tab to "query and connections".

If they've hidden the tab it might be because it's conflicting stuff in the background
https://support.microsoft.com/en-us/office/start-the-power-pivot-add-in-for-excel-a891a66d-36e3-43fc-81e8-fc4798f39ea8

?

When you open your power query and look at the data transformation view, the "profile from 1000 rows" is in the bottom left corner.

https://learn.microsoft.com/en-us/power-query/data-profiling-tools

https://support.microsoft.com/en-gb/office/profile-data-to-view-statistics-power-query-79616636-43aa-428f-b14b-f9c5c060f6b2#:~:text=By%20default%2C%20Power%20Query%20profiles,based%20on%20entire%20data%20set.

combine number and text:
https://excelchamps.com/power-query/concatenate/#:~:text=%22%26%5BLast%5D-,Combining%20Text%20and%20Number%20into%20One%20Column,get%20a%20new%20custom%20column.

When you open your power query and look at the data transformation view, the "profile from 1000 rows" is in the bottom left corner.

All this is greyed out.

I'm going to have one more run then start again

Actually no. Starting again is better.

They've got you on lock down.

If you follow Training Ground Guru on twitter loads of these come up. Man City seems at the forefront of it all
https://x.com/ground_guru

Text.From should fix it. Unless you've got the result column formatted as a number in which case you'll get an error.

I'm pretty sure the version of Excel with a Power Query tab is old. It got brought into Data tab a year or two ago.

Helper columns are the way to go in Power Query as you can strip them out later but things can still depend on them. Also (and this can be a frustating biggie if you're not aware) formulas are case sensitive, that one made me swear a lot originally.

There's a nice list of functions here under Functions if you weren't aware
https://learn.microsoft.com/en-us/powerquery-m/

Likewise, I could be paranoid but I think they've reduced the number or processors(?) or something on my VM. My ram is the same but I'm routinely maxing out my machine using xls.

I made a file a while back to benchmark Excel. Runs a macro to generate some random numbers and do some lookups, sumif, etc a number of times and times it.

5 iterations takes about 45 seconds on my work laptop (which is a couple of years old)

1 Attachment

• Benchmarks-expanded.xlsm