ฝัง
- เผยแพร่เมื่อ 23 มี.ค. 2023
- Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
In this video, we will learn about some best practices that can help us to be more efficient & smart working on Power Query!
Additionally Helpful Video Links:
• Reference an Intermedi...
• M Tricks in Power Quer...
Join this channel to get access to perks:
/ @goodlychandeep
===== ONLINE COURSES =====
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co.in/blog/
Corporate Training 👨🏫 - www.goodly.co.in/training/
Need my help on a Project 💻- www.goodly.co.in/consulting/
Download File ⬇️ - goodly.co.in/5-best-practices...
===== CONTACT 🌐 =====
Twitter - / chandeep2786
LinkedIn - / chandeepchhabra
Email - goodly.wordpress@gmail.com
===== WHO AM I? =====
A lot of people think that my name is Goodly, it's NOT ;)
My name is Chandeep. Goodly is my full-time venture where I share what I learn about Excel and Power BI.
Please browse around, you'd find a ton of interesting videos that I have created :) Cheers! - วิทยาศาสตร์และเทคโนโลยี
Great tips! Thank you. Here is another suggestion for naming steps: Remove blanks & special characters that cause the step to be named #”Step Name” in the Advanced Editor. I find that the M code is much easier to read & maintain.
Please give examples. Thanks
@@txreal2 Rather than naming the query like Step Name, call them StepName
@@KamranMumtazAhmed Thanks!
Very important. Makes it easier to ref previous steps and queries.
Of course queries should also be named as a single word.
It would be nice to have a power query settings to do this by default.
So helpful! Especially re-naming steps. Don't know why I never thought to do this. Thanks!
Love the "bin the red" - it's a great way to try to get rid of as much hard coding as possible. I also remove the spaces in step names - it makes them much easier to read in the M code. I didn't know about the QAT in Power Query! Great tip.
Very awesome video! I love it when a fellow Excel & Power Query enthusiast shares their best practices!
Thank you for creating/sharing this video!
Superb, as usual.✨Apart from your content, I would also like to appreciate your lighting. Very soothing.
I loved the column names tip. So easy and yet so helpful. Thanks.
Great tips! Thank you. I always look forward to your new videos.
Thanks a lot for sharing. Always a great pleasure to listen to you teach these stuff. My PQuery skills have improved greatly since I started watching your videos.
Gracias, ya estoy poniendo en practica tus "Best Practicces"!!!!!!👍👍
Great content! Also love your two interview videos with Chandoo and Matt Arlington. I feel they were ground breaking in that they presented a peek into the mind sets of two leaders within the power bi community. BTW you too are becoming one of the leaders 😊
This is exactly what I needed today!
The way you picked the column names.. that is really a catchy one.. Other techniques are also very useful. Thanks for all of your videos.
To add multi line comments, start with /* and end with */ or use the shortcut key ALT + SHIFT + A
The shortcut key for single line comments is CTRL + /
Very precious lesson!!! Thank you 👍👍
Awesome! Excellent video.
Excelent tips. Thank you for share them
Really liked the Expand Column names tip Goodly, will use that in my queries. As for Quick Access Toolbar, whenever I liaise with staff at work I consistently teach them about this tip and they should use it because that is one of the best shortcut tips ever !
Thanks for these tips! Will also review videos on staging queries. 👍🏽👍🏽
Always a pleasure to watch these videos
A master of your craft. This is so useful. Liked and subbed.
You are Amazing Sir.
Kept your goofs (and composure) in your video. Great work here !
All tips are great and practical and I reccomend everyone to watch this video... I would also like to recommend one very important best practice... do not overcomplicate your queries especially when dealing with large data as this can slo down response time significantly.. what is complex unfortunately you can learn this only thru experience
Thank you very much for these Tips that are really important to have everything more organized.
Greetings from Chile
Thanks Francisco!
Awesome tips…
This is super awesome...just quick question..
Great Stuff!
Again a great video all types and levels of users Chandeep... 😀 I can highly recommend these Videos and Courses...
Removing red was so cool.
❤ very good advice!
Thank you for share your Best Practices!!! That's great!!!
Glad you like them Odel!
Thanks for priceless tips!😊
I think commenting have to raise power query skills, but frequently there are a lot of tasks you have to finish "yesterday", so you just quickly finish one and switch to another.🤔
This is an excellent video! Great tricks!
Thanks J.J. !
Thanks a lot 🙏
Great content and insights !
Glad you think so Farhan!
Genius! Thank you, well done as always. Love your videos
Glad you like them Jhon!
And dynamic tip (about reds) is the best!🙋
Very helpful content, thank you very much
Glad it was helpful Karim!
This is great way of presentation. I just started a channel focusing more on Data Analyst with advance excel... was looking for inspiration on how to present and I got here... :)
i am fan of power query，i am fully agree with your suggestion and i am already done like your sugeestion in my data processing work，good summay
Great video! Thanks!
Glad you liked it Alan!
Love your content and really helping me get better at using Power Query!
I was wondering whether you have any content regarding working smarter when getting data from multiple sources and then my Queries panel has loads of Transform file from query folders for each import.
Such files as Sample file, parameter, transform file and transform sample file appear and I'm not quite sure if I can be smarter with how its all constructed.
Beginner trying to benefit from using Power Query
Really great video, thank you Chandeep!
Thanks Miss Microsoft !
Many useful tips, Thank you.
Glad it was helpful Anbarasu !
The best! Cheers man
Thanks Man!
I have a deep respect for you
thank you
Great tips - I’ve been using 1-4 based on your videos. 5 is def welcome addition
Great to hear Emil!
Thanks
Nice - I like to use folders for my queries - grouping Parameters, stagging and loading
Loved the tip about adding comments, I've added them in the editor but did not know I could view them in the steps pane.
Glad it was helpful William!
You're the best! Waiting for your M Language Course.
Working on it Raimundo!
awesome
"Omit the Red" 😂 ... this is so good advice. Never knew how to say it more concise .
Love the QAT tips: I usually have my QAT below the ribbon!
Great video and Thanks for sharing!
Indians' English vocabularies are way good than those of native speakers. Even though I live in Pakistan but I admit it.
Ah love the parameter idea
Awesome Ian !
Great tips, many thanks! Just one small point. When you say to 'bin the red', I don't know if it's my eyesight or my system settings, but to me, that hard-coded text is brown, rather than red. Is there any way I can change the colour of it?
Hello Goodly, do you know how to dynamically filter list of date ?
Paaji u r awesome
Now you are teaching very clearly
I mean not very fast 😅🥰🥰
Thanks Chandeep!! , really useful tips 👍. Avoid the red and quick access toolbar, my favourites.
Glad you like them Antonio!
@Chandeep - when using the Table.ColumnNames best practice, how does one overcome a situation if a column name in original table exists in expanded table?
Does making the parameter the source hard code it in a way we can’t change the source from the change source settings?
Mind blowing video . I still wonder while practising. In excel data provided by my friend I can see only few tabs ( no hidden tabs) but when I try loading to power query there are many sheets available in front of me. What is that!
For me, one of the best practices is to format the query. Otherwise, the M Language will be extremely messy. I don't want to make by brain hurt.
Usually for the paths that my queries need I have them in a worksheet so that any user not knowing PQ can edit the paths if needed in Excel. I just give each of those cells a name and import from table/range. Then, when I have them in PQ I drill down to have a scalar instead of a table with a single column and a single row. Do parameters offer any kind of advantage over the method I use or is it just a matter of preferences?
The maintainability of a separate excel file that contains the path is bit of hassle. I'd rather have a parameter to edit in Power BI itself. One less data source would keep the model and the model owner happier.. I guess :D
I usually use same technique as Rubén in Excel for the same reason he cites. Very few of our organization’s users know PQ, so storing parameters in the worksheet enables users to change them.
I also do the same. I share my report with colleague who can do my backup. They only have to change the folder path in excel and refresh their report.
Excellent set of tips Chandeep, I suggest to promoved it in pdf quick guides for shared it
Love the idea
Can you stage the power query to an email box that has many different attachments with different formatting. Then use that to pull the data from the staged power query and built your table in excel from each email? Ultimately append all these received emails into one table since all of the needed criteria is there but just in different formats.
the problem with getting column names from files is that in large data the query refresh increases a lot
What would change for a parameter pointing to a sharepoint folder?
Hi Chandeep
I have a Column with various tables
Each of tables have different number of columns
want to limit all tables to 11 number of Columns , any suggestions or link which will be helpful
Nice
Thanks Stranger!
Hi l. I have Matrix visual with some row headers and one column header which is month. This month column will update every month. Now I am unable to sort the values based on each month as it is not showing sort symbol for month header. How can I sort values now. Please make a video
Its a great idea to fetch the column names from the previous table or steps, Is it suitable for huge dataset?
Yes.. Should not be a problem
Plz make videos on SQL
Thanks Chandeep great tips there!
Edit: Chandeep in the 4th example can we erase Query 1,2,3 once we have created the path and function?
I think the idea there is not to erase queries 1,2 and 3 but to update the Source step in those Queries with the parameter rather than hard-coding the directory, so you only need to update the parameter value and all the queries that use the parameter will be automatically updated.
Peace be on you
Can you calculate Outstanding Balance of a customer on fifo method for a given period in brackets i.e., 0-30, 31-60, 61-90, 91-120, 121-180 and above 180.
Thank you for your best practices.
I wander how to apply BP 3 to avoid hardcoding column names when I move a column, say from last position to thd second. The order of the column names is by definition shuffled.
Why do you need to reposition columns?
@@GoodlyChandeep let’s say, I need to transform data in a row that has header. For this I generally transpose & work on it. It sometimes require a helper column which gets added as last column - this requires reorder column step as I need this to be as my first column (contains header, so I can transpose and use first row as header). As the number of rows are dynamic it breakers my query.
I use a way out by naming helper column as “1.” Or “A-“ and by creating list of headers and ascending it helps me to be used in reorder step function. Wanted to know if there is more sophisticated manner to tackle.
@@giteshkariya579 Can you send me some sample data or a query that you've created. I'll try to make a video on this.
In general for larger datasets I wouldn't advice transposing. You need a bit of navigation techniques to select the row or the column that you want.
This might help th-cam.com/video/XH3hWI6stIQ/w-d-xo.html
@@GoodlyChandeep Thank you for answering. Thake thise case. I import a table with the files of a folder:
let
Source = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content],
File_Path = Source{0}[Path],
GetFolderFiles = Folder.Files(File_Path)
in
GetFolderFiles
The file names begin with [Year]&"."&[Period]&"-"[FIle Name]&"."&[xlsx]
I want only to extract anything after "-".
= Table.AddColumn(GetFolderFiles, "New Name", each Text.AfterDelimiter([Name], "- "), type text)
This generates a column "New Name" at the end of the table, as last column.
If I want to transpose this new column, say, on second position.
= Table.ReorderColumns(Text_After_Delimiter,{"Content", "New Name", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
Here, the list {"Content", "New Name", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"} is generated by the command. It cannot be referred dynamically to another list.
Oh, cool, I did not know you could rename steps and add comments. Will make it much easier to navigate
Have fun!
Hi Chadeep. Where can I find your training courses for Power Query AND Power BI in general?
goodly.co.in/products/
6:11 how do you let the content of table shows up at the button of window?
Don't click on the table click on the side in the empty space and it shows the preview
👍
You said you would leave links to videos about staging and extracting intermediate steps
Sorry I missed it.
th-cam.com/video/weafpG2yG1w/w-d-xo.html
th-cam.com/video/-xhnpar6fmw/w-d-xo.html
In the first point of best practice, is it better to rename the steps without using "space". Any advantage of doing this.
Referencing the steps becomes neater.
= #"Changed Type" versus = ChangedType
@@GoodlyChandeep 🙋🏻
The Path trick did not work with my Folder Path, as my Folder Path was to a Sharepoint folder. Trying to find a way around this issue.
= SharePoint.Files(Path)
Hello Chandeep,
I’ve a query, think will help me out,
I have a table workers which contains name, rolenum, start date and end date
And one more table called rates with rolenum, Rate, start date and end date.
How can I lookup the appropriate rate, the problem here is we might have more than one rate for particular roles, let’s say one rate for first 3 months of the year and some other rates for rest of the year, which we can came to know with start and end dates in rates table and at the same time we need to consider start and end dates of the employee into consideration……
Can anyone help me on this , Thanks in Advance.
I can’t wait for Microsoft to incorporate chatGPT into Power Query so it applies a rename step on its own after it’s aware of what that step is doing.
Can you rename steps an already done query? Will referencing be messed up? I am not an expert so just want to know :)
No I think.. It should be fine!
@@GoodlyChandeep btw awsome video...just what i needed
I leave default step names cause it will be hard for aliens to read my queries.😂