FANTASTIC! I'm going to now practice this on my data to refine it until it's a muscle memory. Great tactic!!!
@learnspreadsheets
Жыл бұрын
Yes! It takes a while but totally worth it!
@gborka
3 жыл бұрын
Hot topic. Thanks a lot. Avoid all this endless columns headers referencing is a big deal. Keep going you doing a great job.
@learnspreadsheets
3 жыл бұрын
Thanks man! This was a monster video to make ❤️
@tomhaase1386
4 ай бұрын
I have seen some other solutions, but your was great. I subscribed your channel now. ❤
@learnspreadsheets
4 ай бұрын
That’s so nice to read! Yes when I took the time to learn the process combining ideas I made this video so whenever I need to remember it I go back to the video 😃
@franciscocardenas4499
2 жыл бұрын
Great. I've been dealing with this for some time. Thanks to you I can optimize mi files! Great job!
@learnspreadsheets
2 жыл бұрын
Glad you find it useful 🙃
@Eternal_Stone
Жыл бұрын
Awesome tutorial - great work around and excellently explained
@learnspreadsheets
Жыл бұрын
That’s so kind! Thanks so much 😃
@contabilidad_del_futuro
2 жыл бұрын
Great tips, thanks Dave. Could you suggest a video to classify the text based on some keywords from another table? For example, the sentence is "grocery expense", the query finds the word "grocery" and classifies "1 food expense". Another statement "electricity bill" and classify electricity as "household expense." And so.
@learnspreadsheets
2 жыл бұрын
Hey, thanks for the feedback. Here are some of mind you can try: kzitem.info/news/bejne/uH6usap-jKuIlqg kzitem.info/news/bejne/q26g16emnHOpd3Y and kzitem.info/news/bejne/mo9-p45ue2NymKw but unsure if they will do exactly as you request
@cristiancifuentes4149
Жыл бұрын
Great tips! I started using PowerQuery and your tutorial was very helpful. Any chance you could send me the custom code?
@learnspreadsheets
Жыл бұрын
Thanks Cristian. I am offering the files for my newer videos here www.xlconsulting-asia.com/youtube-files.html but unfortunately some of my older videos I wasn’t doing that for so if you can’t find that specific file maybe some others may be helpful there
@CorneTraa
2 жыл бұрын
Thanks David this looks great. Could you transfer the workbook you used in the video?
@learnspreadsheets
2 жыл бұрын
Thanks, unfortunately I don’t have that file I have some from my newer videos but not this one
@arunachaleswaran
2 жыл бұрын
Nice!!!!!!!!!!!!!!!!! ... I was looking for this only .. Thanks much.. WIll it work for scenarios where the rows are like 300k ?
@arunachaleswaran
2 жыл бұрын
I got the reply too!!!! Thanks
@learnspreadsheets
2 жыл бұрын
Great! It should work with many rows yes!
@omirek2
Жыл бұрын
Is there any hack for Group By? I need to make sum across all the columns, grouped by specific column. I have all setup without column references, but this is where I'm getting stuck.
@learnspreadsheets
Жыл бұрын
Hmm group by is for rows not columns, maybe you could transpose first & then group by? It’s hard to understand exactly what you need through a short message sorry
@omirek2
Жыл бұрын
@@learnspreadsheets Thanks for responding. I mean I'm trying to aggregate rows, yeah - but basically in each column except one. That's why I said all "columns". I can't get away without referencing column names. Maybe there's a way to reference a column by index?
@lgendwila
2 жыл бұрын
This is incredibly helpful. Thank you so much for sharing!
@learnspreadsheets
2 жыл бұрын
Glad you like it!
@gregsmith2547
10 ай бұрын
Will this do anything for increasing refresh speed?
@learnspreadsheets
9 ай бұрын
This adds more steps so it may show down refreshing sadly, but its really a case by case basis
@theoriginalinvisible
3 жыл бұрын
Really nice David, thanks!
@learnspreadsheets
3 жыл бұрын
Glad you liked it
@ScottKeene70
3 жыл бұрын
Thanks! Lot's of very useful tips here. I don't quite understand what's going on in the sorting hack at 04:30 - Reorder. (EDIT - I missed the "Transpose" step!) (You did inspire me to play around with Column From Examples and find some really quick ways to do transformations though!)
@learnspreadsheets
3 жыл бұрын
Thanks for the feedback! Yeah I love these tricks 🙃. Col by examples has many use cases!!
@markbaxter1309
2 жыл бұрын
Hi David, really REALLY useful video. I have been trying to find a solution to this type of issue for ages. You mention in the video that you can send a copy of the file if needed. How can I get a copy? - Thanks again.
@learnspreadsheets
2 жыл бұрын
Heya! Glad you like it! Subscribe to my channel then email me & I’ll try to hunt it down! David@xlconsulting-asia.com
@cesartozzi
3 жыл бұрын
Thank you very much David. It really helped me.
@learnspreadsheets
3 жыл бұрын
You are welcome!
@emilenescheepers8089
2 жыл бұрын
Such a useful video thanks! Learning something new every day in PQ Just have one question, if I format my column (like what you did at 3mins into the video), it creates a new column on my side instead of amending the current column I selected. How can I change this?
@emilenescheepers8089
2 жыл бұрын
I did this workaround in one step thats fine but just wondering why it was creating new columns for every format = Table.TransformColumns(Table.TransformColumns(TransposedTable,{{"Column1", Text.Trim, type text}}),{{"Column1", Text.Proper, type text}})
@learnspreadsheets
2 жыл бұрын
Heya! So there’s a transform tab and an add column tab. Many commands exist in both but they differ in that regard. The first one will change it in place, the latter will add a new column. The code is different too as you spotted
@leemarkin
2 жыл бұрын
Dear sir, I have a set of data that don't have headers, I load it to power query and pq automatically read it as header and change it if have same value (eg. 100%2, 100%3), even I demote it in samples I still can't avoid it, any idea?
@learnspreadsheets
2 жыл бұрын
Power query sometimes automatically promotes headers, this will be a step in your query pane on the right, click the x to delete that step called “promoted headers” and maybe another one that says “changes data type”, if that doesn’t work try this if your dataset is from the same excel workbook. Excel may try to make your data into a table first & load those headers before launching power query. To avoid this don’t use the table & use a named range instead by selecting the relevant rows & columns then type a name in the name box in the top right (in native excel not power query)
@mireyamorales9334
2 жыл бұрын
Genius!
@learnspreadsheets
2 жыл бұрын
Glad you like it!
@FRANKWHITE1996
Жыл бұрын
subscribed! 🙌
@learnspreadsheets
Жыл бұрын
Thanksss, hope you find my content useful!
@mtstans
3 жыл бұрын
OMG THANK YOU SO MUCH!
@zxccxz164
2 жыл бұрын
This is great, but what i am dying to know is: How can i hover over a COLUMN HEADER and show the Description in of the field from the meta data. Or some other longer description. ie Think baseball....column header (field name) ERA, hover and show Earned Run Average OR Store EARNED RUN AVERAGE as the field name, somehow have a NAME ALT of field as ERA. Be able to show ERA as column header and Earned Run Average as hover over. This is SOOOOOO important to keep good descriptive name conventions AND conserving precious space on the canvas
@learnspreadsheets
2 жыл бұрын
Hey power query doesn’t let you store metadata about a column unfortunately, you can store that info in a separate table perhaps?
@zxccxz164
2 жыл бұрын
@@learnspreadsheets there is the description property (also in SSAS tab model), but you can't access it. (or at least in 5 years i have seen no one access it. I suppose you could store in separate table. But still no real way to use it (easily) Some how you can pop up tool tip table, but filtering to the metadata would be difficult?? So annoying that i am wasting thanksgiving wondering why no one else finds this irritating. Oh that and you can't turn on Bookmarks by default in Power BI Report Server.
@MrHhubi
Жыл бұрын
Chaotic recording
@learnspreadsheets
Жыл бұрын
Thanks for the comment but sorry you feel that way, it’s aimed at those with quite a lot of power query experience already
@Bhavik_Khatri
2 жыл бұрын
Could you please email a copy of this file?
@learnspreadsheets
2 жыл бұрын
It’s been a a while but email me & I’ll seee if I can find it. David@xlconsulting-asia.com
Пікірлер: 51