What is the best training course I can pay for to help me with learning Microsoft Excel Power Query's?

[Space Lynx]

My company I work for is willing to pay for it, but I have to find a legit program and then submit the proposal to be approved by HR, etc.

I know I can probably find something for free, but I mean they are offering to pay for it so I would prefer to get the best training I can get, problem is I have to find it and then submit the request. I have no idea where to even look for something like this...

edited title: I misunderstood

 

[Bill_Bright]

Are you looking for on-line training or formal classroom? If formal classroom, you will have to ask the authorities in the Kepler system for what they have available locally in your area.

It also might help to know what version of Excel, and whether a part of a stand-alone Office, or Office 360.

Until then, are you just wanting to learn or are you looking for certification training?

Did you check out Microsoft itself? As seen here, they have several Excel video training courses, including one for Power Query.

 

[Space Lynx]

I just use the latest version of Excel, I am guessing the 360 one, since the company pays for the sub each year.

Don't need a certificate, just wanting to learn.

Online only.

Thank you for the link, that will be a good starting video to watch. I really am not sure what is expected of me from the company, so I need to ask some questions myself before I can answer anymore questions... ty

 

[unwind-protect]

Wouldn't it be better to learn a proper programming language? Some languages can be used from Excel.

 

[Bill_Bright]

I really am not sure what is expected of me from the company

It used to really bug me when upper management would was so vague.

Wouldn't it be better to learn a proper programming language? Some languages can be used from Excel.

I would hope it would not require learning a new - or any - programming language. That's one of the things that makes Excel so nice and powerful, no programming needed. You certainly don't need to be a programmer to become proficient with Excel.

In any case, the OP needs to see what the bosses are wanting before we speculate on what is needed or best to achieve it.

 

[Steevo]

Years ago MS and the company I worked for paid for a course on Excel and Access DB, it was at the local community college and we got a full thick ass book to keep, very worthwhile.

Work Smarter with Microsoft Excel

Offered by Microsoft. This course is intended for learners with some experience using Microsoft Windows, who are seeking to build ... Enroll for free.

www.coursera.org

Looks like MS still puts them on but without the classroom and books. As far as the company paying, ask for course credit/hour pay, If a course is 80 hours ask for your base wage or a percent of if if you will be doing it off the clock.

 

[Bill_Bright]

And if the course costs money, it better come with a cert.

Our local community college offers such courses too. One thing nice about that is typically community colleges are accredited and many of the courses either count towards a degree, or result in a cert you can actually cite on your resume/CVE, or both.

 

[Frick]

I would hope it would not require learning a new - or any - programming language. That's one of the things that makes Excel so nice and powerful, no programming needed. You certainly don't need to be a programmer to become proficient with Excel.

This is also a downside because it lets people do stuff in Excel they really shouldn't.

 

[bug]

My company I work for is willing to pay for it, but I have to find a legit program and then submit the proposal to be approved by HR, etc.

I know I can probably find something for free, but I mean they are offering to pay for it so I would prefer to get the best training I can get, problem is I have to find it and then submit the request. I have no idea where to even look for something like this...

edited title: I misunderstood

There's always Excel's help. But if you have to pay for it, then idk.

 

[eidairaman1]

Are you looking for on-line training or formal classroom? If formal classroom, you will have to ask the authorities in the Kepler system for what they have available locally in your area.

It also might help to know what version of Excel, and whether a part of a stand-alone Office, or Office 360.

Until then, are you just wanting to learn or are you looking for certification training?

Did you check out Microsoft itself? As seen here, they have several Excel video training courses, including one for Power Query.

He needs to find a BCIS course

 

[unwind-protect]

The most advanced Excel features are not easier to learn than a reasonable programming language. The the real language has many more applications.

 

[Bill_Bright]

This is also a downside because it lets people do stuff in Excel they really shouldn't.

Huh? I don't understand. I mean I understand what you are saying but it really does not make any sense. The delete key on my keyboard lets me do stuff I shouldn't. So do my mouse buttons.

User discipline is always a factor. If someone does not know what a function does, they shouldn't use it without learning about it first.

Instead of the pessimistic, half-empty view, try being an optimistic, half-full type. The upside is those features let people do all sorts of good, time saving things in Excel to make their jobs easier and more efficient.

Still, mistakes happen so fortunately, there's still Ctrl-z and of course, backups.

The most advanced Excel features are not easier to learn than a reasonable programming language.

Bullfeathers! That may be true for you, but you clearly do not speak for everyone. In fact, you don't speak for the vast majority of users! NO ONE should have to learn a programming language in order to work with data in spreadsheets. That would be so archaic.

I am sorry, but you are just wrong to suggest - as a blanket statement that applies to all - learning a programming language is easier to learn than Excels advanced features. Most users are fully capable of learning how to use those features just fine, without knowing how to code. That's pretty much the point for having GUIs for programs and operating systems.

I've been working with computers since the mid 1970s. I've spent much of my career AVOIDING programming. I hate programming. I have no desire to learn any programming language and I certainly am not alone. I was one of 10 hardware techs in a company with 400 programmers. One of my "other hats" was as an alpha and beta tester. All that did was enforce my desire to never want to program.

If you enjoy and prefer coding, that's great! The world needs good coders. But do not assume you represent the majority for clearly, you don't.

 

[bug]

The most advanced Excel features are not easier to learn than a reasonable programming language. The the real language has many more applications.

I've always said when you get to the advanced parts of Excel, what you really need is a DB (can be as simple as SQLite). But there's no stopping people from using the right tools for the wrong purpose...

 

[Frick]

Huh? I don't understand. I mean I understand what you are saying but it really does not make any sense. The delete key on my keyboard lets me do stuff I shouldn't. So do my mouse buttons.

User discipline is always a factor. If someone does not know what a function does, they shouldn't use it without learning about it first.

Instead of the pessimistic, half-empty view, try being an optimistic, half-full type. The upside is those features let people do all sorts of good, time saving things in Excel to make their jobs easier and more efficient.

Still, mistakes happen so fortunately, there's still Ctrl-z and of course, backups.

I'm talking about those hyper complicated spreadsheets that are essentially databases but because it's Excel instead of actual database software they're much, much worse, amongst other things. You can make stuff in Excel you really shouldn't, which is both good and bad.

But I digress.

Bullfeathers! That may be true for you, but you clearly do not speak for everyone. In fact, you don't speak for the vast majority of users! NO ONE should have to learn a programming language in order to work with data in spreadsheets. That would be so archaic.

I am sorry, but you are just wrong to suggest - as a blanket statement that applies to all - learning a programming language is easier to learn than Excels advanced features. Most users are fully capable of learning how to use those features just fine, without knowing how to code. That's pretty much the point for having GUIs for programs and operating systems.

I've been working with computers since the mid 1970s. I've spent much of my career AVOIDING programming. I hate programming. I have no desire to learn any programming language and I certainly am not alone. I was one of 10 hardware techs in a company with 400 programmers. One of my "other hats" was as an alpha and beta tester. All that did was enforce my desire to never want to program.

If you enjoy and prefer coding, that's great! The world needs good coders. But do not assume you represent the majority for clearly, you don't.

The "most advanced" Excel stuff is literally programming dude. Macro stuff. VB stuff.

But again I digress.

 

[eidairaman1]

I'm talking about those hyper complicated spreadsheets that are essentially databases but because it's Excel instead of actual database software they're much, much worse, amongst other things. You can make stuff in Excel you really shouldn't, which is both good and bad.

But I digress.



The "most advanced" Excel stuff is literally programming dude. Macro stuff. VB stuff.

But again I digress.

For db stuff there is MS Access

 

[Bill_Bright]

he "most advanced" Excel stuff is literally programming dude. Macro stuff. VB stuff.

I understand what you are saying but that is not the point I was making. It was suggested users learn a new programming language just to use the advanced features in Excel. (1) That is not necessary and (2) by far, the vast majority of users don't need or use those extreme features of Excel.

In my opinion (as a non-programmer), much of the problem in learning those extreme advanced features is simply understanding what the features do - their concepts, for example.

Back in the day, I had to setup network monitoring between the state's mainframe and 300 nodes in 93 counties across the state. My boss wanted statistics and analysis etc. I'm a hardware tech, but that's what they wanted. Understanding what pivot tables did was the hardest part. Once I realized what a pivot table was, using Excel's menus to create and maintain the spreadsheet with pivot tables was easy - even for me. And again, I hate programming and avoid it like the plague.

 

[neatfeatguy]

Find out what your company expects so you're not wasting only your time, but theirs as well.

A dozen plus years ago, a job I worked they made use of SQL in their new software for database use, but their original software was based on MS Access. Outside of one guy that programmed for the old software no one else at work knew how to functionally use Access. I taught myself how so I could go through and rebuild and fix broken databases for customers. I know nothing about SQL, but Access was my jam. I'd pull apart broken data, fix issues that would normally be done by basically scrapping a database and replacing it with a new one, thus causing loss of tons of data. After figuring this out I turned my eye toward Excel and learned a bunch of useful tools.

I had taught myself all sorts of tricks using Excel because it was for something that not only benefited me at the time, but also something that could be used to benefit my work. I cobbled together an excel form for doing inventory. Incorporated all the customer names, all of our part numbers that link up to customer's part numbers, specific packing instructions, lengths of material and so on using VLOOKUP. But, my current job doesn't require use of either of these tools and the longer you go without using them the more and more you forget about it. I don't think I can do 75% of the things I used to be able to do in Excel anymore without help.

As I said at the start before my rambling, find out what their expectations are first and go from there. Learning to use Excel on your own isn't that difficult, but I guess it may depend on what the company expects of you.

 

[Space Lynx]

Find out what your company expects so you're not wasting only your time, but theirs as well.

Working on that now, this is what I found out basically I need to take some data from one Excel area they give me, and put it in a new Excel, but with different formatting. I assume learning more Power Query would help in this process.



So, in academia, I take this information they give me in a Excel spreadsheet - then I have to put it into a into a separate Excel spreadsheet to generate code for my system to read, I need some way of comparing the original excel information they give me with mine, to double check all the information is correct... is there an easier way to do this besides manually comparing them? If there is no way that is fine, I am just curious.

 

[Bill_Bright]

What you are describing is simple cut and paste.

 

[Space Lynx]

What you are describing is simple cut and paste.

no worries for now though, i will get clarification next week and revisit this issue. tbh, i don't think they even know what they are doing, so yeah im just leaving it be for now

edit: nm I do not have to generate code, I need a quick way of comparing the data in it's original form - basically the company is saying create a new format with this data we have given you, i do copy and paste most of that over, but they just want it in different format, then when i am done with that, i am trying to make sure i make no errors

again, will revisit next week when i figure this out more clearly, my apologies for wasting everyones time, i just thought power query would help me

 

[Bill_Bright]

tbh, i don't think they even know what they are doing, so yeah im just leaving it be for now

That sounds like an opportunity for you to shine!

 

[Wirko]

no worries for now though, i will get clarification next week and revisit this issue. tbh, i don't think they even know what they are doing, so yeah im just leaving it be for now

edit: nm I do not have to generate code, I need a quick way of comparing the data in it's original form - basically the company is saying create a new format with this data we have given you, i do copy and paste most of that over, but they just want it in different format, then when i am done with that, i am trying to make sure i make no errors

again, will revisit next week when i figure this out more clearly, my apologies for wasting everyones time, i just thought power query would help me

Power Query seems like an overkill for such a task - then again, "a new format" might mean something very simple (reordering columns, doing a few calculations in each row, replacing some texts with others, using lookup tables, filtering out some data, applying conditional format to point out errors). Or it may be a complex extraction-transformation-loading, which Power Query seems to be a good fit for (a good example is given on MS's starting page). But don't (yet) proceed with PQ if you're not fluent with operations I listed as "simple".

Here's another thought, not related to technology. You're going to work with data, possibly of unknown origin and of unknown quality. Be prepared for all kinds of errors. You'll come across people born in the year 2169 and invoices issued in the year 0012. One retail store here has electronic price tags with discounted prices, and the original price is sometimes NaN. Try to trace the data to the origin and find out what was entered manually - that's a common source of errors. Don't assume it was properly checked when it was entered. So much better if you can get paid for such work.

That sounds like an opportunity for you to shine!

"Let's give this guy some more work that no one else can do!"

 

[Bill_Bright]

"Let's give this guy some more work that no one else can do!"

That too. But "no one else can do it" means job security!

 

[unwind-protect]

Not everybody hates programming. Whoever hates programming won't be happy with advanced Excel either. Some languages can be used in Excel, especially if databases come in.

Just saying that for the same learning effort there might be bigger payoff.

 

[Steevo]

Programming and Excel/Access pivot tables really opened up data mining for me. And many functions in Excel are easier understood with more advanced math learning, programming just taught me that with some thinking I could make C++ or excel do the work for me.