Excel People

[Rayvin 5217]

7 hours ago, Gemmill said:

Absolutely absurd that there hasn't been a HUGE response to my post above.  Basically if you're doing ANYTHING with data in a spreadsheet, using Power Query will probably earn you at least a day off a month. 

 

Ok I'll bite. How are you using it and what is it doing to help you. I use excel a lot but haven't tried this.

[Gemmill 44803]

6 minutes ago, Rayvin said:

 

Ok I'll bite. How are you using it and what is it doing to help you. I use excel a lot but haven't tried this.

 

Basically if you have any need to import, clean, combine data - or basically do ANYTHING with data, you should be doing it with Power Query.  It's only been around since 2016 - it's a free add-in, but it doesn't seem to be that well known. 

 

It has this user interface where you can connect to data files (in loads of formats) or just to the data in your current workbook, transform it, and then load it back into the workbook where you can either just do analysis on that data, or you can combine it with other data sources, and if needs be create an entire data model. But during the transform process you can unpivot the data and you can add new calculations in. 

 

So if you get some completely fucked up text file, you can tidy it all up, etc. But the kicker is that once you do the connect/transform/load process once, it remembers all the steps you took (like VBA but without the need to know/understand VBA) and will just repeat them on a revised version of the data in seconds. And if you've made a mistake, you just go back, delete the saved step and re-perform it the right way, and it remembers that.

 

There's loads more to it than the above, but that's a basic explanation.  The book that I'm learning it from tries to explain it with these graphs - they're saying you can learn a bit of SQL, a good chunk of VBA, and a shitload of formulas to become a super Excel guru.  With a really long learning curve on all of them.  Or you can learn Power Query in next to no time and get 80% of the impact of all of the above: 

 

 

 

This should help explain it better than I could: 

 

 

 

[Christmas Tree 4725]

24 minutes ago, Rayvin said:

 

Ok I'll bite. How are you using it and what is it doing to help you. I use excel a lot but haven't tried this.

Edited April 3, 2022 by Christmas Tree

[Gemmill 44803]

You're not invited to this party, CONEHEED. 

[Gemmill 44803]

I should have mentioned as well, Power Query works in Power BI too.  So once you've learned it in Excel, you've learned it in Power BI as well.  Two tools for the price of one. 

[Rayvin 5217]

Ok yeah this does actually look helpful. I'm building a sheet at the moment that could benefit, I'll give it a shot and report back.

 

Thanks!

[Gemmill 44803]

1 minute ago, Rayvin said:

Ok yeah this does actually look helpful. I'm building a sheet at the moment that could benefit, I'll give it a shot and report back.

 

Thanks!

 

Nice one, this is a good, up-to-date book (published Nov 2021) if you wanted to learn more about it. 

 

https://www.amazon.co.uk/Master-Your-Data-Excel-Power/dp/1615470581/ref=sr_1_1?crid=2HILCKW7M4Q0A&keywords=master+your+data&qid=1648996327&sprefix=master+your+data%2Caps%2C92&sr=8-1

 

[MrBass 2651]

21 hours ago, Gemmill said:

 

Basically if you have any need to import, clean, combine data - or basically do ANYTHING with data, you should be doing it with Power Query.  It's only been around since 2016 - it's a free add-in, but it doesn't seem to be that well known. 

 

It has this user interface where you can connect to data files (in loads of formats) or just to the data in your current workbook, transform it, and then load it back into the workbook where you can either just do analysis on that data, or you can combine it with other data sources, and if needs be create an entire data model. But during the transform process you can unpivot the data and you can add new calculations in. 

 

So if you get some completely fucked up text file, you can tidy it all up, etc. But the kicker is that once you do the connect/transform/load process once, it remembers all the steps you took (like VBA but without the need to know/understand VBA) and will just repeat them on a revised version of the data in seconds. And if you've made a mistake, you just go back, delete the saved step and re-perform it the right way, and it remembers that.

 

There's loads more to it than the above, but that's a basic explanation.  The book that I'm learning it from tries to explain it with these graphs - they're saying you can learn a bit of SQL, a good chunk of VBA, and a shitload of formulas to become a super Excel guru.  With a really long learning curve on all of them.  Or you can learn Power Query in next to no time and get 80% of the impact of all of the above: 

 

 

 

This should help explain it better than I could: 

 

 

 

 

Lucky for me I'm shit hot at VBA coding, SQL queries AND know a fuck ton of Excel formulas so, by that token, I must be a guru level Excel user. On top of that plethora of skills, I can update Excel spreadsheets using PowerShell so I don't think there's a single word that can define my skill level... although I'm sure by virtue of this post some of you tarts will think of one. 

[Tom 14011]

Definitely going to give Power Query a go. Im

upping my excel skills at the moment and have been on it a few months but this it’s a lot better if there’s a tool to save me bothering  

 

I’ve got some nasty databases(one is over 2m lines)  at the moment so if there’s an easier way of doing it I’m all ears. 

[Howmanheyman 33108]

Was thinking of getting some excel/word training for the future when I'm kicked onto the dole next year but after reading this thread I'm starting to think it's a step too far.

[Gemmill 44803]

1 hour ago, Tom said:

Definitely going to give Power Query a go. Im

upping my excel skills at the moment and have been on it a few months but this it’s a lot better if there’s a tool to save me bothering  

 

I’ve got some nasty databases(one is over 2m lines)  at the moment so if there’s an easier way of doing it I’m all ears. 

 

Aye you could put this in Power Query, then just load it as a connection into a workbook (rather than as a table). If you load as a connection, the fact that the number of rows exceeds Excel's 1.2m max is irrelevant because you're not trying to display the whole database in a workbook.

 

Then you can just query and analyse the data however you like with Pivot tables or Power Pivot. 

[Gemmill 44803]

1 hour ago, Howmanheyman said:

Was thinking of getting some excel/word training for the future when I'm kicked onto the dole next year but after reading this thread I'm starting to think it's a step too far.

 

My lass is in the same boat. Fucking hates teaching but feels like 20 odd years in that profession has left her without the basic office skills she would need to get out. 

 

She's doing some courses on Skillshare atm, but honestly you could easily get canny good on all the Office apps by just finding some good YouTube channels and following their tutorials for nowt. 

[Howmanheyman 33108]

7 minutes ago, Gemmill said:

 

My lass is in the same boat. Fucking hates teaching but feels like 20 odd years in that profession has left her without the basic office skills she would need to get out. 

 

She's doing some courses on Skillshare atm, but honestly you could easily get canny good on all the Office apps by just finding some good YouTube channels and following their tutorials for nowt. 

When I saw I had a notification in the Excel thread I thought it would be CT telling me to take up taxi driving instead.

[Tom 14011]

38 minutes ago, Gemmill said:

 

Aye you could put this in Power Query, then just load it as a connection into a workbook (rather than as a table). If you load as a connection, the fact that the number of rows exceeds Excel's 1.2m max is irrelevant because you're not trying to display the whole database in a workbook.

 

Then you can just query and analyse the data however you like with Pivot tables or Power Pivot. 

Ill definitely give it a go. Is it something I’m gonna need IT to sign off or is it part of the excel program anyway?

[Gemmill 44803]

4 minutes ago, Tom said:

Ill definitely give it a go. Is it something I’m gonna need IT to sign off or is it part of the excel program anyway?

 

Just comes with Excel. If you go to the data tab, Power Query is all the Get Data from... stuff. I started learning it about a week ago and I've just laced something that took me an afternoon to fuck on with normally. It took a bit of setting up (I was writing Power Query formulas to do some of the stuff, which I somehow got right :lol:) but from now on I just hit the refresh button and it literally takes that long to make it work. An afternoon a month saved, during which time I'll be sitting watching the telly instead. 

 

[Gemmill 44803]

5 hours ago, MrBass said:

 

Lucky for me I'm shit hot at VBA coding, SQL queries AND know a fuck ton of Excel formulas so, by that token, I must be a guru level Excel user. On top of that plethora of skills, I can update Excel spreadsheets using PowerShell so I don't think there's a single word that can define my skill level... although I'm sure by virtue of this post some of you tarts will think of one. 

 

I'm gonna go with "clever cunt", but see what others think and we'll set up a poll.

 

[Christmas Tree 4725]

3 hours ago, Howmanheyman said:

When I saw I had a notification in the Excel thread I thought it would be CT telling me to take up taxi driving instead.

Funnily enough I did consider that earlier but didn’t have you down as a driver  

[The Fish 10846]

15 hours ago, Gemmill said:

 

Just comes with Excel. If you go to the data tab, Power Query is all the Get Data from... stuff. I started learning it about a week ago and I've just laced something that took me an afternoon to fuck on with normally. It took a bit of setting up (I was writing Power Query formulas to do some of the stuff, which I somehow got right :lol:) but from now on I just hit the refresh button and it literally takes that long to make it work. An afternoon a month saved, during which time I'll be sitting watching the telly instead. 

 

We're switching from this to Google Data Studio at work. 

 

Also trying to learn enough SQL, that I can make a business case to send me off somewhere to sit exams in it. Which has fuck all to do with accreditation and everything to do with having a jolly on the work dime.

[wykikitoon 20110]

Right you sexy Excel fuckers.

I have been trying to streamline my cycling league table thing for my club using VLOOKUP.

I have it working sweet for one table.  But I now want it to filter something.  I maybe doing this wrong of course.  Below a couple of screen shots.

First one is a full table of male and female competitors.  Ranked in order of total points.

Second one, I want the same but only famale riders.  However I am not sure how to sort this one?

 

 

 

[The Fish 10846]

1 hour ago, wykikitoon said:

Right you sexy Excel fuckers.

I have been trying to streamline my cycling league table thing for my club using VLOOKUP.

I have it working sweet for one table.  But I now want it to filter something.  I maybe doing this wrong of course.  Below a couple of screen shots.

First one is a full table of male and female competitors.  Ranked in order of total points.

Second one, I want the same but only famale riders.  However I am not sure how to sort this one?

 

 

 

What are the tables B5:C41 and H5:I41?

 

 

You'd honestly be better with a pivot table, then you can filter with a click of a button

 

[wykikitoon 20110]

I shall post a screen shot when home but it's a list of names etc. If it can be simplified and still automated in some way then bonus

[The Fish 10846]

I won't see it until tomorrow, but yeah it can 100% be simplified and automated because I'm a boss.

[wykikitoon 20110]

I have tried looking at a pivot table.  It does look like its the way to go, but I am struggling to use it.  If I knew how to host a cut down version of my spreadsheet it maybe an easier way to see what I am trying to do.

Basically we have a list of events for the season.  If you do an event you get points.  Its the person with the most points at the end of the season wins.  Thats the jist of it.

[The Fish 10846]

Just screen shot the raw data and I'll have a bash.

[wykikitoon 20110]

Right, screen dump alert