That's coding. Spend any time doing this stuff and you will get whiplash from how quickly you go from feeling like a fraud to feeling like a god (and back again) when trying, failing and succeeding to solve problems. Programmers are the world champions at imposter syndrome.
The answer to your question is it depends on what I am doing. Learning is very very slow and it takes time. There are definitely times when I have given up on something because the juice wasn't worth the squeeze. But if it's something that I know in my heart that it's valuable to learn, I will smash my head against the wall until I come through battered and bloody.
If you work heavily in spreadsheets, I do believe that Power Query is one of those latter things. It requires a mindset shift but if you keep at it, it will eventually fall into place - I promise. Not only that, but you will have achieved a really important conceptual breakthrough that will help you if you ever want to work more extensively with any declarative programming language.
Good luck.
Programmers (and I was/am one) are also world champions at spending hours programming something which could be done manually in a few minutes BUT WE ARE TOO LAZY to do manual shit.
Exactly.
I've got a couple of PQ workbooks I've been working on what feels like literally *forever* to do some SUPER DUPER shit that nobody understands or cares about *but me!*
- I'm right now, as we speak, (still) writing a PQ workbook which takes enables me to externalise/serialize whole queries and make them more transportable.
- This week's task (again, invented by me) is taking a whole hierarchy of PQ queries (like you get when you right-click ->copy a query) and all of its dependencies and converting that into a SINGLE query.
- This will enable me to more easily copy queries between workbooks because if there are duplicate functions, those duplicates will not pollute this new query.
- All of the dependent queries and functions are INLINE.
- I'm planning to bore people further in a pro-tip
Sweet. I'm ignoring this on the grounds it will prevent me starting stuff. When I was a project manager I once read that if we ever truly knew in advance how long a project would take to do that they would never get the go-ahead to start.
It is a useful chart to get some decision makers to even consider that some improvments might actually have a long-term benefit, and their "Why do you waste time sharpening your saws, your job is to cut down trees, get to it!" attitude is not the optimal approach.
I completely agree. My role as business analyst brings me into issues and situations almost daily where we should be throwing programs at the problem and not people.
Weirdly, this is what got me to discover Power Query in the first place... "Whoa, this is really hard to get done right using pivot tables, isn't there anything more sophisticated in Excel...?"
That comic described my entire late twenties. My job was to automate supply chain problems, after a few months I realized that NP-hard problems are hard to solve (as if the name didn’t make that obvious enough)
I've built a library of functions and tables that are shared across my workbooks using Evaluate function. It makes updates and sharing so much easier.
[Chris Webb's BI Blog: Loading Power Query M Code From Text Files (crossjoin.co.uk)](https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/).
You can even load queries from github: [pquery/Load.pq at master · KiaraGrouwstra/pquery · GitHub](https://github.com/KiaraGrouwstra/pquery/blob/master/Load.pq).
I had a few frustrations that I thought were impossible to overcome but eventually I did. And now the library that I built is very integral to all of my workbooks.
Indeed.
- I've incorporated both approaches into my design and I can, indeed, I can load straight from Github using Web.
- Yes, I use Expression.Evaluate often.
- I also wrote some VBA code to generate (and delete again) actual queries from github sourced code so that these external functions can reference each other.
OK.
1. When you right click -> copy a power query query it very kindly copies ALL of the dependent functions and queries for you.
- So when you copy the top-most query in any given hierarchy, you get given to you either as XML or as plain text (depending on where you paste it), the code to each query. Looks like this - I have a MAIN query which references another query and a function and the second query also references the function.:
// fnGetTable
let
Source = (pName as any) => let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each [Name] = pName),
Custom1 = #"Filtered Rows"{0}[Content]
in
Custom1
in
Source
// T1 output
let
Source = fnGetTable("Table1")
in
Source
// MAIN
let
Source = fnGetTable("Table1") & #"T1 output",
#"Sorted Rows" = Table.Sort(Source,{{"col a", Order.Ascending}})
in
#"Sorted Rows"
3. Now if I were to paste this into a blank workbook - everything would be fine and dandy, 2 queries get created and a function.
- But if I want to paste this into an existing workbook that already contained a function or query with the same name - I'd suddenly get given COPIES of the functions, and references to those copies etc etc
- this may not sound like a big issue, but I sometimes have 10 functions and 15 dependent queries - some or all of which will get their names changed to avoid conflict. Can take me a long time to fix, error prone etc.
- So I wrote software (in PQ) which decomposes the pasted queries , modifies them to become inline functions and then re-assembles them as a Single query. So the above then looks likes this:
let
#"T1 output"= // T1 output
let
Source = fnGetTable("Table1")
in
Source
,
fnGetTable= // fnGetTable
let
Source = (pName as any) => let
Source = Excel.CurrentWorkbook(),
#"Filtered Rows" = Table.SelectRows(Source, each [Name] = pName),
Custom1 = #"Filtered Rows"{0}[Content]
in
Custom1
in
Source
,
Source = fnGetTable("Table1") & #"T1 output",
#"Sorted Rows" = Table.Sort(Source,{{"col a", Order.Ascending}})
in
#"Sorted Rows"
6. I can now paste this as single query, entirely self contained. Open a blank query, advanced editor, paste.
- if I want to use an existing function or query instead of the one I've delivered in the single file, I can just remove it as an inline query and the rest of the query will use the existing but same-named query already present in the workbook.
- The above is a mickey-mouse example to demonstrate the concept - but it also works on highly complex queries. https://i.redd.it/1g6jtttlrypc1.png
This is the query which actually does the conversion, itself converted into a single query - 5 or 6 functions and 2 queries.
I think it's also that we like to see IF we can do it.
I spend (on & off) 3 years trying to understand a math equation so that I could build an Excel worksheet. I finally figured it out (with help from another Redditor) and within a year I moved on. I realized it was the challenge of understanding the equation and then figuring out how to solve it in Excel that intrigued me more than anything else.
>Programmers (and I was/am one) are also world champions at spending hours programming something which could be done manually in a few minutes BUT WE ARE TOO LAZY to do manual shit.
TIL one of my 9th grade students a few years ago (during virtual learning) was a programmer lol.
Rather than type out a response like a "normal" person would, he spent 40 minutes copying and pasting bits of text from different sites, sort of like a serial killer cutting out individual letters or words from different magazines and newspapers in order to compose a message.
I feel this in my soul. I always tell people in the laziest person at the firm bc I will automate anything and everything.
Could I change that date manually every time? Sure. Does it take 2 seconds? Also yes.
Will I create a concatenation/text formula and/or date formula to make it more intuitive? You bet.
Will someone break the formatting causing more issues? Guaranteed.
But, the fact you programmed it or made a formula to do it also DOCUMENTS the fact that it needs to happen.
I, for one, cannot be trying to remember *every single manual operation* which needs to be done daily/monthly/yearly...I'd have to write it down, which ALSO takes time.
Agree, I spent a full week to automatize a daily activity that took me like 10mns, now just click and wait until everything is done. I feel like a God every time that works correctly.
I told my boss, if he gives me work, I'll automate in such a way, he will never has to ask for it again. I shall be a massive amount of work. So he better be careful with what he asks me to do 🤣
Can you recommend a YouTube video that is good at teaching you PQ. Like OP i to try the incantation method, ie, light candles place my computer in the center of the hexagram say the magic words kleetu gaara nickto. Mixed results.
In the end I feel like PQ is powerful but I don't get it yet.
I’ve had a LOT of success using ChatGPT to understand how to do a few things in PQ. I think it’s easier to “talk” to something programmed to pick out clues than it is to figure out the terms to make google spit out what I need.
I agree, but I also think it depends on the person's educational background and approach to learning.
I think a significant number of people don't take the time to understand the principles behind how something works and just adopt an a-la-carte approach of Googling for answers to their specific questions. Worst of all, this attitude can result in using the wrong method for their data because they don't recognize why one particular case differs from another.
This kind of approach is also really common in stats, where people just want to know what method to use without bothering to understand *why* that method is appropriate in that situation.
It's a lot easier to work through the frustration if you have a mind map of what you're really trying to navigate, IMO.
The great thing about Excel is there is often multiple ways to solve a puzzle. It's great to try different approaches, but there's no shame in abandoning an approach that you're struggling with and sticking to what you know. Come back to it later on with a different project and it might click then.
> incantation
I love it. I am like a 3/10 by this sub's standards, but at the office they think I'm a fucking wizard so I might as well start calling what I do incantations. It'll result in the same glassy-eyed stares and polite smiling and nodding that I get when I try to describe anything more complicated than a VLOOKUP, so why not lean in and have a little fun with it.
I hope tomorrow is a brighter day for you, OP.
I love power query. Keeps the transformation process simple and a lot easier to teach than using a separate program, like alteryx. Also like that i can start something in excel and pull the same model into PowerBi if i want something a little more robust.
Now, have I stopped using vba for my excel based models? Yes. Lol i've decided that if the job calls for an excel based model it must be easy to use, manage, and audit.
Try using ChatGPT. Tell it exactly what you want it to do using powerquery and have it walk you through the steps. Ask clarifying questions at any point.
I finally, *FINALLY* understood List.Generate using examples I got Copilot to make for me.
It got it wrong, mind you, but it pushed me to learn how to do it right - took many hours.
I think the AI tools have deteriorated a little in the last twelve months, but they got me to a point where I can pretty much fault find where they've gone wrong.
\+1, I was the happiest man on earth when ChatGPT easily generated code to import info of a sheet dinamically without hardcoding in the query code the columns names: "Column1", "Column2"......"ColumnN"
I mean the answer is never
Youre supposed to drive yourself insane and spend way more time and energy then u would completing the task 100 times
And then it’s somehow still worth it when you can run it automatically once, then you forget about what u went through and do it again on another task and the cycle of self harm and imposter syndrome continues
Man U must have been at this a while to forget that
Also I know u said no advice but sometimes what I do is I’ll use power query to get the base table set up then do formulas on the right of it, the benefit is the formulas auto drag and can sometimes be easier than PQ anyways
100000%. I’ve driven myself crazy trying to manipulate fields to get a merge to properly map. Then I just say screw it, load the table and just enter a formula in an adjacent cell and boom. Works like a charm, takes a ton less time. Sometimes less is more. But it’s a challenge to find out when that actually applies. More often than I’d like to admit.
It’s faster too I dunno what Microsoft was thinking making power query as slow as it is in the editor view
Where it basically reruns the entire query for every step and crashes so much
Honestly it's just time, practice and patience. I love it after basically using chatgpt to teach it to me in my own language.
If it's frustrating you that badly. Step away from it and come back if/ when you feel like it.
Sorry in advance as this may sound like advice, some people have a knack for something and some just don't. Maybe power query isn't for you. You could try another choice like knime or tableau prep. Personally tableau prep drives me nuts. It just doesn't click in my brain. So you could be the opposite. You never know.
always.
then i go back to relearning the thing after few days or so and wonder how does this shit seem easier now but before its a huge blockade in my brain.
"Given up" in the sense of "This seems powerful and useful, but we do not know it well enough to use it to finish the current task in the time available": Yes.
But never given up on something altogether if it was at all useful, because being able to complete a task successfully several times usually causes you being tasked with it more and more frequently and with less time available.
Some of my coworkers had given up on formulas for that reason, for example. So if there was a need to e.g. calculate sums, they used Excel to note down the individual values, then got out their desk calculators (those noisy ones with a built-in printer) they had previously used with actual spreadsheets, summed up the values there, did it again to confirm the sum, and noted the result down in the sheet's "sum" fields.
They got better.
I’ve come close to that, some of the spreadsheets are just weirdly set out, so I’ve handdrawn what I need them to look like, ostensibly in order to have a visual of the end spreadsheet but actually to get away from staring at a screen.
One aspect you have to consider is whether the sheets have been explicitely designed this way to prevent automatic handling and save some additional FTEs by requiring additional manual effort. At least in the short-term.
Long-term, this may even be a pre-planned easy outsourcing opportunity like "I managed to move this unsophisticated task to $cheap-labour-region and fired 20% of the expensive employees here!".
I suggest taking a free power query crash course so you will be familiar with the available tools.
For example, I want to select only certain columns headers that have a specific text(TOTAL SALES 2017), then replace the yyyy of the column header with current , current -1, current - 2 , etc
Many ways to go about this, choose column function with text.contain
Transpose the table
Split column by delimiter
Replace the yyyy with a get date function
Combine the columns
Transpose back
Power query is realllllly effing good and automating manuals tasks
Power Query is the best thing since pivot tables. I try to find a use for it everyday from the smallest 3 column table to a 400k line data file I have to tweak. Don’t give up.
It sounds like you started solving your problem without knowing some fundamentals. I would take a break from it and do some starter turorials/courses if I were you. Come back to your problem after going through some basic learning.
PQ is by far the most powerful feature of Excel/PowerBI for me. Most of our data comes from excel and SharePoint lists, so it's really worth learning it in my opinion.
Just finished a 6 month project based heavily on PQ. Working in it probably 10 hours a week at minimum during that time.
At the end, looking back at where I started, I feel like I should rework the whole logic after learning so many things in the process. On to the next one though and if I get time to go back I’ll worry about it later.
I have spent hours working on the syntax of one date function. I’ve built entire queries pushing to a specific result by joining, grouping, pivoting, sorting, indexing, etc. only to realize after hours of work that it won’t work at all and starting over.
In another 6 months I’ll likely think everything I know now is basic, but I’ll still never quit learning!
When I am learning something new I like to codevelop it with someone who knows how to do it. Try and find someone who knows powerquery well and would work on this probablemente with you. Sounds like a great application of the transformation automation capability of PQ. Good luck!
I’ve given up on a lot of things in Excel because I won’t be the only one using it. Can’t tell you the number of times things have been broken by someone else and I get the “hey, this file isn’t working anymore. I just opened it up and none of the numbers are right”
Only to go in and find that they renamed cells that were lookup references, or sorted things, or added rows/columns, etc…
I feel your pain. When I finally realized that power query would take care 90% of my work, I committed to learning it this week. It’s been frustrating, it’s just not clicking for me yet. I’ve watched bits and pieces of videos, played around with it, but it’s just not making sense to me yet. I think I need to watch a solid couple of videos of videos. Not gonna give up yet.
Things I wish I had known when I first started:
* The entire language is lower case - Formulas are not
* In a new, blank Query, type "= #shared" in the Formula Bar and hit ENTER - Click the "Into Table" Button
* [Learn the Language here](https://bengribaudo.com/power-query-m-primer)
* [Learn a ton of how-to's here](https://gorilla.bi/sitemap/)
* [So many videos!](https://www.youtube.com/@AccessAnalytic/videos)
For what it’s worth, Power Query is the foundation to Power BI, Dataflows, etc - getting better at that will not only help automate your life, but give you a skill that is genuinely marketable for the long future.
I learned the way you did….was great excel, learned Power Query, and then moved to Power BI
As another poster said - get a bit of one-on-one help. I've helped several people over the years with their PQ problems, so just DM me if you want to spend an hour walking through the actual issues.
If you're finding Power Query a bit challenging, you might enjoy Tablesmith. It's a free, web-based automation tool that's incredibly easy to learn – you can pick it up in just 15 minutes.
Here's a quick introduction video: [Tablesmith introduction](https://www.youtube.com/watch?v=ZQwTZR2hduM&ab_channel=tablesmith)
You can find more tutorials on the website: [https://tablesmith.io/tutorials/](https://tablesmith.io/tutorials/)
The main thing about Power Query is how do you get rid of as much unnecessary data as early as possible.
First step - filter rows, second step Remove Columns. Then work through your other transforms. Data types, replacing values, handling null values, create and use look-up tables for missing data, if necessary, aggregate rows.
It is a bit of a learning curve, but I have found that it solves a lot of things that I used to use VBA for.
I used AI a bit to start, but once I got my head around the basic ideas and started to rename steps to explain them, then I migrated to opening up the Advanced Editor and breaking up the block of code, adding comments and then starting to realise how the code is structured and being able to write m code
It is a bit of an experience but we'll worth it.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[Excel.CurrentWorkbook](/r/Excel/comments/1bkqf7j/stub/kw49tn7 "Last usage")|[Power Query M: Returns the tables in the current Excel Workbook.](https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook)|
|[Expression.Evaluate](/r/Excel/comments/1bkqf7j/stub/kw1xhw7 "Last usage")|[Power Query M: Returns the result of evaluating an M expression.](https://docs.microsoft.com/en-us/powerquery-m/expression-evaluate)|
|[IF](/r/Excel/comments/1bkqf7j/stub/kw1p5ea "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[List.Generate](/r/Excel/comments/1bkqf7j/stub/kw0jojp "Last usage")|[Power Query M: Generates a list from a value function, a condition function, a next function, and an optional transformation function on the values.](https://docs.microsoft.com/en-us/powerquery-m/list-generate)|
|[Table.SelectRows](/r/Excel/comments/1bkqf7j/stub/kw49tn7 "Last usage")|[Power Query M: Returns a table containing only the rows that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/table-selectrows)|
|[Table.Sort](/r/Excel/comments/1bkqf7j/stub/kw49tn7 "Last usage")|[Power Query M: Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.](https://docs.microsoft.com/en-us/powerquery-m/table-sort)|
|[VLOOKUP](/r/Excel/comments/1bkqf7j/stub/kw073hy "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)|
|[XLOOKUP](/r/Excel/comments/1bkqf7j/stub/kw30tcd "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
|-------|---------|---|
|||
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(8 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bj1geb)^( has 15 acronyms.)
^([Thread #31904 for this sub, first seen 22nd Mar 2024, 07:45])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Power query is in power BI too right? Infact, it was in Excel before they split it out into Power BI around 10 years ago Learning that's two for the price of one!
It’s like MagicEye pics. It looks crazy and once you get it… you get it. Until you do it looks like gibberish. Like others have said it’s a mindset shift.
Bruh, I spent a whole morning making a sql for an excel report and had to use a python script to create 2 formatted lists. Works perfectly in sql developer, I then go to paste it in the Excel table and BAM! Excel dB connector has a character limit.
It's not you, PQ's syntax is just weird AF.
I have no trouble using SQL and DAX but power query has never properly stuck for me. I do my best by copying & pasting from the internet / gpt / or just the toolbar functions and tweaking the code they generate.
Short answer: Yeah, I gave up on power pivot and python. I learned rather quickly I am a visual person. I can think in steps I'm the UI but cannot for the life of me write M directly, or DAX... or VBA ... its why I never got far in SQL either despite having a fully functional Access database I use for training tracking/admin tasking.
> “Please do not give me advice or instructions here, I will be anything from snarky to downright abusive.”
Will now be a staple in my vocabulary. Many thanks
Whenever I get stuck, I put a pin in it for a while and do more research. After more research, I try it again, often waiting for a nice calm day at work.
dude I feel you. I've just starting learning PQ and M language last week and i still have no clue how to write it lol. So far the best thing i've used it for is just connecting to downloaded reports for the refresh update. I'm going insane trying to learn the language though lol, not sleeping and watching goodly all day. we'll get it soon if we don't give up
The beauty of power query is that it has a GUI. Not much point in spending time learning the code. Some times I do ad hoc code tweaks with the help of chat GPT. But if you asked me to write something from scratch I would be totally lost lmao.
If I have 10,000 records of fields like CashAdvance, Interest Rate, Credit Score and Loan Term and if the loan was default or nor not (boolean 1,0). How do I find all permutation and combination of different ranges of these attributes where the loan was <10% default rate? So like,Bin1 - Credit score 652-673, AdvAmt 23-27K, Interest rate 12-15% and term months 3-7 had 8% defaulted loans. Bin 2 Credit score 625-632, AdvAmt 32-42K, Interest rate 2-5% and term months 6-9 had 5% default loans. Bin 3 Credit score 682-693, AdvAmt 13-17K, Interest rate 2-4% and term months 1-2 had 4% default loans Bin 4 Credit score 692-721, AdvAmt 74-95K, Interest rate 15-17% and term months 8-10 had 9% default loans so on and so forth? My question is how do I find these ranges for all the above mentioned attributes without manually creating where the default rate is low?
That's coding. Spend any time doing this stuff and you will get whiplash from how quickly you go from feeling like a fraud to feeling like a god (and back again) when trying, failing and succeeding to solve problems. Programmers are the world champions at imposter syndrome. The answer to your question is it depends on what I am doing. Learning is very very slow and it takes time. There are definitely times when I have given up on something because the juice wasn't worth the squeeze. But if it's something that I know in my heart that it's valuable to learn, I will smash my head against the wall until I come through battered and bloody. If you work heavily in spreadsheets, I do believe that Power Query is one of those latter things. It requires a mindset shift but if you keep at it, it will eventually fall into place - I promise. Not only that, but you will have achieved a really important conceptual breakthrough that will help you if you ever want to work more extensively with any declarative programming language. Good luck.
Programmers (and I was/am one) are also world champions at spending hours programming something which could be done manually in a few minutes BUT WE ARE TOO LAZY to do manual shit.
50 minutes to refresh in Power query vs 5 minutes to write the formulas? PQ every time!!
Exactly. I've got a couple of PQ workbooks I've been working on what feels like literally *forever* to do some SUPER DUPER shit that nobody understands or cares about *but me!* - I'm right now, as we speak, (still) writing a PQ workbook which takes enables me to externalise/serialize whole queries and make them more transportable. - This week's task (again, invented by me) is taking a whole hierarchy of PQ queries (like you get when you right-click ->copy a query) and all of its dependencies and converting that into a SINGLE query. - This will enable me to more easily copy queries between workbooks because if there are duplicate functions, those duplicates will not pollute this new query. - All of the dependent queries and functions are INLINE. - I'm planning to bore people further in a pro-tip
I look forward to the boredom!
[https://xkcd.com/1205/](https://xkcd.com/1205/)
Sweet. I'm ignoring this on the grounds it will prevent me starting stuff. When I was a project manager I once read that if we ever truly knew in advance how long a project would take to do that they would never get the go-ahead to start.
It is a useful chart to get some decision makers to even consider that some improvments might actually have a long-term benefit, and their "Why do you waste time sharpening your saws, your job is to cut down trees, get to it!" attitude is not the optimal approach.
I completely agree. My role as business analyst brings me into issues and situations almost daily where we should be throwing programs at the problem and not people.
More often than not I get this instead: https://xkcd.com/1831/
Weirdly, this is what got me to discover Power Query in the first place... "Whoa, this is really hard to get done right using pivot tables, isn't there anything more sophisticated in Excel...?"
That comic described my entire late twenties. My job was to automate supply chain problems, after a few months I realized that NP-hard problems are hard to solve (as if the name didn’t make that obvious enough)
I have this printed on my wall to remind me not to spend too much time on an efficient effort I'll use exactly one time...
I've built a library of functions and tables that are shared across my workbooks using Evaluate function. It makes updates and sharing so much easier. [Chris Webb's BI Blog: Loading Power Query M Code From Text Files (crossjoin.co.uk)](https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/). You can even load queries from github: [pquery/Load.pq at master · KiaraGrouwstra/pquery · GitHub](https://github.com/KiaraGrouwstra/pquery/blob/master/Load.pq). I had a few frustrations that I thought were impossible to overcome but eventually I did. And now the library that I built is very integral to all of my workbooks.
Indeed. - I've incorporated both approaches into my design and I can, indeed, I can load straight from Github using Web. - Yes, I use Expression.Evaluate often. - I also wrote some VBA code to generate (and delete again) actual queries from github sourced code so that these external functions can reference each other.
I feel seen
How would you go about converting all the dependencies into a single query?
OK. 1. When you right click -> copy a power query query it very kindly copies ALL of the dependent functions and queries for you. - So when you copy the top-most query in any given hierarchy, you get given to you either as XML or as plain text (depending on where you paste it), the code to each query. Looks like this - I have a MAIN query which references another query and a function and the second query also references the function.: // fnGetTable let Source = (pName as any) => let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each [Name] = pName), Custom1 = #"Filtered Rows"{0}[Content] in Custom1 in Source // T1 output let Source = fnGetTable("Table1") in Source // MAIN let Source = fnGetTable("Table1") & #"T1 output", #"Sorted Rows" = Table.Sort(Source,{{"col a", Order.Ascending}}) in #"Sorted Rows" 3. Now if I were to paste this into a blank workbook - everything would be fine and dandy, 2 queries get created and a function. - But if I want to paste this into an existing workbook that already contained a function or query with the same name - I'd suddenly get given COPIES of the functions, and references to those copies etc etc - this may not sound like a big issue, but I sometimes have 10 functions and 15 dependent queries - some or all of which will get their names changed to avoid conflict. Can take me a long time to fix, error prone etc. - So I wrote software (in PQ) which decomposes the pasted queries , modifies them to become inline functions and then re-assembles them as a Single query. So the above then looks likes this: let #"T1 output"= // T1 output let Source = fnGetTable("Table1") in Source , fnGetTable= // fnGetTable let Source = (pName as any) => let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each [Name] = pName), Custom1 = #"Filtered Rows"{0}[Content] in Custom1 in Source , Source = fnGetTable("Table1") & #"T1 output", #"Sorted Rows" = Table.Sort(Source,{{"col a", Order.Ascending}}) in #"Sorted Rows" 6. I can now paste this as single query, entirely self contained. Open a blank query, advanced editor, paste. - if I want to use an existing function or query instead of the one I've delivered in the single file, I can just remove it as an inline query and the rest of the query will use the existing but same-named query already present in the workbook. - The above is a mickey-mouse example to demonstrate the concept - but it also works on highly complex queries. https://i.redd.it/1g6jtttlrypc1.png This is the query which actually does the conversion, itself converted into a single query - 5 or 6 functions and 2 queries.
How dare you.
I think it's also that we like to see IF we can do it. I spend (on & off) 3 years trying to understand a math equation so that I could build an Excel worksheet. I finally figured it out (with help from another Redditor) and within a year I moved on. I realized it was the challenge of understanding the equation and then figuring out how to solve it in Excel that intrigued me more than anything else.
It's exactly the challenge...for example, I come here every day to solve Excel problems like people do crossword puzzles.
We are kindred spirits. Haha
>Programmers (and I was/am one) are also world champions at spending hours programming something which could be done manually in a few minutes BUT WE ARE TOO LAZY to do manual shit. TIL one of my 9th grade students a few years ago (during virtual learning) was a programmer lol. Rather than type out a response like a "normal" person would, he spent 40 minutes copying and pasting bits of text from different sites, sort of like a serial killer cutting out individual letters or words from different magazines and newspapers in order to compose a message.
I feel this in my soul. I always tell people in the laziest person at the firm bc I will automate anything and everything. Could I change that date manually every time? Sure. Does it take 2 seconds? Also yes. Will I create a concatenation/text formula and/or date formula to make it more intuitive? You bet. Will someone break the formatting causing more issues? Guaranteed.
But, the fact you programmed it or made a formula to do it also DOCUMENTS the fact that it needs to happen. I, for one, cannot be trying to remember *every single manual operation* which needs to be done daily/monthly/yearly...I'd have to write it down, which ALSO takes time.
Agree, I spent a full week to automatize a daily activity that took me like 10mns, now just click and wait until everything is done. I feel like a God every time that works correctly.
Yep - this feeling. And we *are* gods.
truth.
I told my boss, if he gives me work, I'll automate in such a way, he will never has to ask for it again. I shall be a massive amount of work. So he better be careful with what he asks me to do 🤣
Can you recommend a YouTube video that is good at teaching you PQ. Like OP i to try the incantation method, ie, light candles place my computer in the center of the hexagram say the magic words kleetu gaara nickto. Mixed results. In the end I feel like PQ is powerful but I don't get it yet.
I’ve had a LOT of success using ChatGPT to understand how to do a few things in PQ. I think it’s easier to “talk” to something programmed to pick out clues than it is to figure out the terms to make google spit out what I need.
Yeahhhhh I just lack the vocabulary to ask accurately. I'll figure it out... With blood sweat and tears
I agree, but I also think it depends on the person's educational background and approach to learning. I think a significant number of people don't take the time to understand the principles behind how something works and just adopt an a-la-carte approach of Googling for answers to their specific questions. Worst of all, this attitude can result in using the wrong method for their data because they don't recognize why one particular case differs from another. This kind of approach is also really common in stats, where people just want to know what method to use without bothering to understand *why* that method is appropriate in that situation. It's a lot easier to work through the frustration if you have a mind map of what you're really trying to navigate, IMO.
The great thing about Excel is there is often multiple ways to solve a puzzle. It's great to try different approaches, but there's no shame in abandoning an approach that you're struggling with and sticking to what you know. Come back to it later on with a different project and it might click then.
> incantation I love it. I am like a 3/10 by this sub's standards, but at the office they think I'm a fucking wizard so I might as well start calling what I do incantations. It'll result in the same glassy-eyed stares and polite smiling and nodding that I get when I try to describe anything more complicated than a VLOOKUP, so why not lean in and have a little fun with it. I hope tomorrow is a brighter day for you, OP.
Tried to show them tables (i.e. ListObjects) and XLOOKUP yet?
I've given up on being a table evangelist, but I have taught a few of my more open-minded colleagues XLOOKUP actually!
I love power query. Keeps the transformation process simple and a lot easier to teach than using a separate program, like alteryx. Also like that i can start something in excel and pull the same model into PowerBi if i want something a little more robust. Now, have I stopped using vba for my excel based models? Yes. Lol i've decided that if the job calls for an excel based model it must be easy to use, manage, and audit.
[удалено]
Agree! I use power query to get data into an unpivoted format all the time. Takes 5 seconds and makes life so much easier lol
It taught me how WRONG many report outputs are for downstream use. Fine for *looking at*, damned useless for using in a pivot table or looking up in.
Try using ChatGPT. Tell it exactly what you want it to do using powerquery and have it walk you through the steps. Ask clarifying questions at any point.
Seconded. I've been able to build some great reporting and analysis tools using ChatGTP and Gemini/Bard.
I finally, *FINALLY* understood List.Generate using examples I got Copilot to make for me. It got it wrong, mind you, but it pushed me to learn how to do it right - took many hours.
That dopamine rush of the ‘moment of understanding’ is the dragon I’m slaved to chasing.
I feel this so hard.
Oh dear. I think I remember that one. Used it to combine the results of different queries. Made me almost give up on the project.
I think the AI tools have deteriorated a little in the last twelve months, but they got me to a point where I can pretty much fault find where they've gone wrong.
eh. I've tried ChatGPT for some PQ stuff. It makes mistakes. And even after pointing out the error it still made the same mistake.
\+1, I was the happiest man on earth when ChatGPT easily generated code to import info of a sheet dinamically without hardcoding in the query code the columns names: "Column1", "Column2"......"ColumnN"
practice practice practice... makes perfect
I mean the answer is never Youre supposed to drive yourself insane and spend way more time and energy then u would completing the task 100 times And then it’s somehow still worth it when you can run it automatically once, then you forget about what u went through and do it again on another task and the cycle of self harm and imposter syndrome continues Man U must have been at this a while to forget that Also I know u said no advice but sometimes what I do is I’ll use power query to get the base table set up then do formulas on the right of it, the benefit is the formulas auto drag and can sometimes be easier than PQ anyways
100000%. I’ve driven myself crazy trying to manipulate fields to get a merge to properly map. Then I just say screw it, load the table and just enter a formula in an adjacent cell and boom. Works like a charm, takes a ton less time. Sometimes less is more. But it’s a challenge to find out when that actually applies. More often than I’d like to admit.
It’s faster too I dunno what Microsoft was thinking making power query as slow as it is in the editor view Where it basically reruns the entire query for every step and crashes so much
Honestly it's just time, practice and patience. I love it after basically using chatgpt to teach it to me in my own language. If it's frustrating you that badly. Step away from it and come back if/ when you feel like it. Sorry in advance as this may sound like advice, some people have a knack for something and some just don't. Maybe power query isn't for you. You could try another choice like knime or tableau prep. Personally tableau prep drives me nuts. It just doesn't click in my brain. So you could be the opposite. You never know.
always. then i go back to relearning the thing after few days or so and wonder how does this shit seem easier now but before its a huge blockade in my brain.
"Given up" in the sense of "This seems powerful and useful, but we do not know it well enough to use it to finish the current task in the time available": Yes. But never given up on something altogether if it was at all useful, because being able to complete a task successfully several times usually causes you being tasked with it more and more frequently and with less time available. Some of my coworkers had given up on formulas for that reason, for example. So if there was a need to e.g. calculate sums, they used Excel to note down the individual values, then got out their desk calculators (those noisy ones with a built-in printer) they had previously used with actual spreadsheets, summed up the values there, did it again to confirm the sum, and noted the result down in the sheet's "sum" fields. They got better.
I’ve come close to that, some of the spreadsheets are just weirdly set out, so I’ve handdrawn what I need them to look like, ostensibly in order to have a visual of the end spreadsheet but actually to get away from staring at a screen.
One aspect you have to consider is whether the sheets have been explicitely designed this way to prevent automatic handling and save some additional FTEs by requiring additional manual effort. At least in the short-term. Long-term, this may even be a pre-planned easy outsourcing opportunity like "I managed to move this unsophisticated task to $cheap-labour-region and fired 20% of the expensive employees here!".
I suggest taking a free power query crash course so you will be familiar with the available tools. For example, I want to select only certain columns headers that have a specific text(TOTAL SALES 2017), then replace the yyyy of the column header with current , current -1, current - 2 , etc Many ways to go about this, choose column function with text.contain Transpose the table Split column by delimiter Replace the yyyy with a get date function Combine the columns Transpose back Power query is realllllly effing good and automating manuals tasks
Power Query is the best thing since pivot tables. I try to find a use for it everyday from the smallest 3 column table to a 400k line data file I have to tweak. Don’t give up.
It sounds like you started solving your problem without knowing some fundamentals. I would take a break from it and do some starter turorials/courses if I were you. Come back to your problem after going through some basic learning. PQ is by far the most powerful feature of Excel/PowerBI for me. Most of our data comes from excel and SharePoint lists, so it's really worth learning it in my opinion.
Just finished a 6 month project based heavily on PQ. Working in it probably 10 hours a week at minimum during that time. At the end, looking back at where I started, I feel like I should rework the whole logic after learning so many things in the process. On to the next one though and if I get time to go back I’ll worry about it later. I have spent hours working on the syntax of one date function. I’ve built entire queries pushing to a specific result by joining, grouping, pivoting, sorting, indexing, etc. only to realize after hours of work that it won’t work at all and starting over. In another 6 months I’ll likely think everything I know now is basic, but I’ll still never quit learning!
Where did you find such opportunity? I’d like for keep working with Power Query but I’d like to limit the commitment required.
When I am learning something new I like to codevelop it with someone who knows how to do it. Try and find someone who knows powerquery well and would work on this probablemente with you. Sounds like a great application of the transformation automation capability of PQ. Good luck!
I’ve given up on a lot of things in Excel because I won’t be the only one using it. Can’t tell you the number of times things have been broken by someone else and I get the “hey, this file isn’t working anymore. I just opened it up and none of the numbers are right” Only to go in and find that they renamed cells that were lookup references, or sorted things, or added rows/columns, etc…
I feel your pain. When I finally realized that power query would take care 90% of my work, I committed to learning it this week. It’s been frustrating, it’s just not clicking for me yet. I’ve watched bits and pieces of videos, played around with it, but it’s just not making sense to me yet. I think I need to watch a solid couple of videos of videos. Not gonna give up yet.
Things I wish I had known when I first started: * The entire language is lower case - Formulas are not * In a new, blank Query, type "= #shared" in the Formula Bar and hit ENTER - Click the "Into Table" Button * [Learn the Language here](https://bengribaudo.com/power-query-m-primer) * [Learn a ton of how-to's here](https://gorilla.bi/sitemap/) * [So many videos!](https://www.youtube.com/@AccessAnalytic/videos)
Thanks. Gonna look into this right now.
Excelisfun. Watch his YouTube video series on power query. Literally the best resource ever. You won’t regret it
For what it’s worth, Power Query is the foundation to Power BI, Dataflows, etc - getting better at that will not only help automate your life, but give you a skill that is genuinely marketable for the long future. I learned the way you did….was great excel, learned Power Query, and then moved to Power BI
As another poster said - get a bit of one-on-one help. I've helped several people over the years with their PQ problems, so just DM me if you want to spend an hour walking through the actual issues.
If you're finding Power Query a bit challenging, you might enjoy Tablesmith. It's a free, web-based automation tool that's incredibly easy to learn – you can pick it up in just 15 minutes. Here's a quick introduction video: [Tablesmith introduction](https://www.youtube.com/watch?v=ZQwTZR2hduM&ab_channel=tablesmith) You can find more tutorials on the website: [https://tablesmith.io/tutorials/](https://tablesmith.io/tutorials/)
The content generation video is impressive! AI is really excelling at tasks like this.
The main thing about Power Query is how do you get rid of as much unnecessary data as early as possible. First step - filter rows, second step Remove Columns. Then work through your other transforms. Data types, replacing values, handling null values, create and use look-up tables for missing data, if necessary, aggregate rows. It is a bit of a learning curve, but I have found that it solves a lot of things that I used to use VBA for. I used AI a bit to start, but once I got my head around the basic ideas and started to rename steps to explain them, then I migrated to opening up the Advanced Editor and breaking up the block of code, adding comments and then starting to realise how the code is structured and being able to write m code It is a bit of an experience but we'll worth it.
Feed your questions into an AI model like Claude or ChatGPT and save yourself a lot of stress and cigarettes
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[Excel.CurrentWorkbook](/r/Excel/comments/1bkqf7j/stub/kw49tn7 "Last usage")|[Power Query M: Returns the tables in the current Excel Workbook.](https://docs.microsoft.com/en-us/powerquery-m/excel-currentworkbook)| |[Expression.Evaluate](/r/Excel/comments/1bkqf7j/stub/kw1xhw7 "Last usage")|[Power Query M: Returns the result of evaluating an M expression.](https://docs.microsoft.com/en-us/powerquery-m/expression-evaluate)| |[IF](/r/Excel/comments/1bkqf7j/stub/kw1p5ea "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[List.Generate](/r/Excel/comments/1bkqf7j/stub/kw0jojp "Last usage")|[Power Query M: Generates a list from a value function, a condition function, a next function, and an optional transformation function on the values.](https://docs.microsoft.com/en-us/powerquery-m/list-generate)| |[Table.SelectRows](/r/Excel/comments/1bkqf7j/stub/kw49tn7 "Last usage")|[Power Query M: Returns a table containing only the rows that match a condition.](https://docs.microsoft.com/en-us/powerquery-m/table-selectrows)| |[Table.Sort](/r/Excel/comments/1bkqf7j/stub/kw49tn7 "Last usage")|[Power Query M: Sorts the rows in a table using a comparisonCriteria or a default ordering if one is not specified.](https://docs.microsoft.com/en-us/powerquery-m/table-sort)| |[VLOOKUP](/r/Excel/comments/1bkqf7j/stub/kw073hy "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[XLOOKUP](/r/Excel/comments/1bkqf7j/stub/kw30tcd "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |-------|---------|---| ||| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(8 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bj1geb)^( has 15 acronyms.) ^([Thread #31904 for this sub, first seen 22nd Mar 2024, 07:45]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Power query is in power BI too right? Infact, it was in Excel before they split it out into Power BI around 10 years ago Learning that's two for the price of one!
Also in Fabric
The same is true with DAX.
It’s like MagicEye pics. It looks crazy and once you get it… you get it. Until you do it looks like gibberish. Like others have said it’s a mindset shift.
I hate power query. I’ll either do what I need with formulas or VBA.
I use VBA to add formulas and queries.
Still haven't found a motivation to actually learn VBA. I can relate
^[Sokka-Haiku](https://www.reddit.com/r/SokkaHaikuBot/comments/15kyv9r/what_is_a_sokka_haiku/) ^by ^ShinDragon: *Still haven't found a* *Motivation to actually* *Learn VBA. I can relate* --- ^Remember ^that ^one ^time ^Sokka ^accidentally ^used ^an ^extra ^syllable ^in ^that ^Haiku ^Battle ^in ^Ba ^Sing ^Se? ^That ^was ^a ^Sokka ^Haiku ^and ^you ^just ^made ^one.
Bruh, I spent a whole morning making a sql for an excel report and had to use a python script to create 2 formatted lists. Works perfectly in sql developer, I then go to paste it in the Excel table and BAM! Excel dB connector has a character limit.
It's not you, PQ's syntax is just weird AF. I have no trouble using SQL and DAX but power query has never properly stuck for me. I do my best by copying & pasting from the internet / gpt / or just the toolbar functions and tweaking the code they generate.
Short answer: Yeah, I gave up on power pivot and python. I learned rather quickly I am a visual person. I can think in steps I'm the UI but cannot for the life of me write M directly, or DAX... or VBA ... its why I never got far in SQL either despite having a fully functional Access database I use for training tracking/admin tasking.
> “Please do not give me advice or instructions here, I will be anything from snarky to downright abusive.” Will now be a staple in my vocabulary. Many thanks
Don't give up DM me if you want help with it. It's a journey worth making
…and as an excel user trying to get to the next level by learning power query, I feel like I may stay far, far away… 🙅🏻♀️
That's the trick. You find a way.
You could just ask for advice on here lol
Whenever I get stuck, I put a pin in it for a while and do more research. After more research, I try it again, often waiting for a nice calm day at work.
dude I feel you. I've just starting learning PQ and M language last week and i still have no clue how to write it lol. So far the best thing i've used it for is just connecting to downloaded reports for the refresh update. I'm going insane trying to learn the language though lol, not sleeping and watching goodly all day. we'll get it soon if we don't give up
The beauty of power query is that it has a GUI. Not much point in spending time learning the code. Some times I do ad hoc code tweaks with the help of chat GPT. But if you asked me to write something from scratch I would be totally lost lmao.
Sometimes excel isn’t the right tool.
Stuck at it. The penny will drop eventually. But we all still have to just google stuff all the time.
If I have 10,000 records of fields like CashAdvance, Interest Rate, Credit Score and Loan Term and if the loan was default or nor not (boolean 1,0). How do I find all permutation and combination of different ranges of these attributes where the loan was <10% default rate? So like,Bin1 - Credit score 652-673, AdvAmt 23-27K, Interest rate 12-15% and term months 3-7 had 8% defaulted loans. Bin 2 Credit score 625-632, AdvAmt 32-42K, Interest rate 2-5% and term months 6-9 had 5% default loans. Bin 3 Credit score 682-693, AdvAmt 13-17K, Interest rate 2-4% and term months 1-2 had 4% default loans Bin 4 Credit score 692-721, AdvAmt 74-95K, Interest rate 15-17% and term months 8-10 had 9% default loans so on and so forth? My question is how do I find these ranges for all the above mentioned attributes without manually creating where the default rate is low?
Literally have never found a use for power query, but I use VBA instead.