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 😃
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.
Hey, thanks for the feedback. Here are some of mind you can try: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-WHxKvHSyWcs.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-J8jqspcAxDA.html and ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9YHAZ8B1Aew.html but unsure if they will do exactly as you request
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
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!)
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.
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.
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
@@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?
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?
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}})
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
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?
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)
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 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.
