Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Power Query
- Thread starter mintjulep
- Start date
You work with queries in PQ desktop, that slightly differs, depending on host application (Excel, Power BI,...). Excel automatically opens PQ desktop when you start a new query. Power Query query is a series of transformations, written in dedicated M language. When you add data transformation in the query, PQ automatically records this action. The final result of query can be extracted to worksheet or left as connection for future use.
It is easy to start to work with PQ, with some experience you will try to modify the recorded code, play with lists, functions, formulas. You can find a lot of tutorials, also from MS. I uploaded some solutions using PQ, for instance thread68-1826627 .
A starting point:
[ul]
[li]you need some data to process, if it Excel, organize them in Excel tables,[/li]
[li]get all tables (one by one) to PQ environment, create queries from external sources or excel tables, with connection only output; if by accident you get the output in new Excel worksheet - delete it,[/li]
[li]you should end up with a series of queries, with one (input only) or two (automatic data transformation) actions, you can rename them if you like,[/li]
[li]start the proper query: create new empty query, start with: =[tt]SourceQueryName[/tt], the result of [tt]SourceQueryName[/tt] will become an input in new query,[/li]
[li]continue transformations (sorting, filtering, rearranging/deleting columns, merging queries etc, using available UI options,[/li]
[li]when you get what you like, simply close PQ desktop and save query (data will be returned to new sheet), or output to selected range.[/li]
[/ul]
It is possible to examine recorded M code in advanced editor (or copy query to notepad).
The code can be modified in some ways: in advanced editor, in selected action command line, or in configuration form for some actions.
M language is case sensitive. At least in Excel, wrong syntax can crash the application, so frequently save the work.
combo
It is easy to start to work with PQ, with some experience you will try to modify the recorded code, play with lists, functions, formulas. You can find a lot of tutorials, also from MS. I uploaded some solutions using PQ, for instance thread68-1826627 .
A starting point:
[ul]
[li]you need some data to process, if it Excel, organize them in Excel tables,[/li]
[li]get all tables (one by one) to PQ environment, create queries from external sources or excel tables, with connection only output; if by accident you get the output in new Excel worksheet - delete it,[/li]
[li]you should end up with a series of queries, with one (input only) or two (automatic data transformation) actions, you can rename them if you like,[/li]
[li]start the proper query: create new empty query, start with: =[tt]SourceQueryName[/tt], the result of [tt]SourceQueryName[/tt] will become an input in new query,[/li]
[li]continue transformations (sorting, filtering, rearranging/deleting columns, merging queries etc, using available UI options,[/li]
[li]when you get what you like, simply close PQ desktop and save query (data will be returned to new sheet), or output to selected range.[/li]
[/ul]
It is possible to examine recorded M code in advanced editor (or copy query to notepad).
The code can be modified in some ways: in advanced editor, in selected action command line, or in configuration form for some actions.
M language is case sensitive. At least in Excel, wrong syntax can crash the application, so frequently save the work.
combo
- Thread starter
- #4
I rarely find tutorials motivating. They're typically trivial examples, and of no relevance to me.
I prefer to wait for a need, start with the assumption that "there must be a way to use the tool", and figure out the solution.
In my particular case, I was building a project estimating sheet. There are direct staff and contractors, which have different overhead rates, and those rates can change year by year. There's salary escalation, that might change each year. There's a fee that might change each year.
I knew that I'd need a table to look up staff billing rate each year.
Getting from those factors to a single lookup table was going to be complicated with formulas. It's a Cartesian product of multiple tables. I was thinking to write a VBA function to do it, but a bit of Googling found a way to use Power Query to do it, and from there I was able to use PQ to do all the calculations as well.
I prefer to wait for a need, start with the assumption that "there must be a way to use the tool", and figure out the solution.
In my particular case, I was building a project estimating sheet. There are direct staff and contractors, which have different overhead rates, and those rates can change year by year. There's salary escalation, that might change each year. There's a fee that might change each year.
I knew that I'd need a table to look up staff billing rate each year.
Getting from those factors to a single lookup table was going to be complicated with formulas. It's a Cartesian product of multiple tables. I was thinking to write a VBA function to do it, but a bit of Googling found a way to use Power Query to do it, and from there I was able to use PQ to do all the calculations as well.
@mintjulep
For Excel problem, with variable sizes of input data, VLOOKUPS and tons of formulas that make the workbook hard to manage, transition to Power Query is a great simplification and motivation to learn it.
Some time ago I traced published (by Access Analytic) Power Query challenges, still can be found in their blog. The solutions, available for downloading, demonstrate the power of PQ and possible different attempts for the same problem. However, in general the problems and solutions are not basic.
combo
For Excel problem, with variable sizes of input data, VLOOKUPS and tons of formulas that make the workbook hard to manage, transition to Power Query is a great simplification and motivation to learn it.
Some time ago I traced published (by Access Analytic) Power Query challenges, still can be found in their blog. The solutions, available for downloading, demonstrate the power of PQ and possible different attempts for the same problem. However, in general the problems and solutions are not basic.
combo
- Status
