5 Best Practices in Power Query

No video

5 Best Practices in Power Query

Рет қаралды 49,065

Жыл бұрын

Check out our newly launched M Language course ↗️ - goodly.co.in/l...
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/l...
✔️ Power Query Course-
goodly.co.in/l...
✔️ Master Excel Step by Step-
goodly.co.in/l...
✔️ Business Intelligence Dashboards-
goodly.co.in/l...
===== LINKS 🔗 =====
Blog 📰 - www.goodly.co....
Corporate Training 👨‍🏫 - www.goodly.co....
Need my help on a Project 💻- www.goodly.co....
Download File ⬇️ - goodly.co.in/5...
===== 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!

Пікірлер: 134

@jimfitch Жыл бұрын

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.

@txreal2 Жыл бұрын

Please give examples. Thanks

@KamranMumtazAhmed Жыл бұрын

@@txreal2 Rather than naming the query like Step Name, call them StepName

@txreal2 Жыл бұрын

@@KamranMumtazAhmed Thanks!

@OmisileKehindeOlugbenga Жыл бұрын

Very important. Makes it easier to ref previous steps and queries. Of course queries should also be named as a single word.

@QuLaZz Жыл бұрын

It would be nice to have a power query settings to do this by default.

@grahamc5531 Жыл бұрын

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 + /

@shirleymoreman6725 Жыл бұрын

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.

@kishangzp Жыл бұрын

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

@Locomaid Ай бұрын

Thank you! I wondered how I could add comments to the renamed steps. Now I know!

@swilson1958 7 ай бұрын

So helpful! Especially re-naming steps. Don't know why I never thought to do this. Thanks!

@bhaskaraggarwal8971 Жыл бұрын

Superb, as usual.✨Apart from your content, I would also like to appreciate your lighting. Very soothing.

@EricHartwigExcelConsulting Жыл бұрын

Very awesome video! I love it when a fellow Excel & Power Query enthusiast shares their best practices! Thank you for creating/sharing this video!

@franciscom.paredesarias2356 Жыл бұрын

Thank you very much for these Tips that are really important to have everything more organized. Greetings from Chile

@GoodlyChandeep Жыл бұрын

Thanks Francisco!

@paspuggie48 Жыл бұрын

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 !

@OmisileKehindeOlugbenga Жыл бұрын

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.

@Rice0987 Жыл бұрын

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.🤔

@winnielu520-by2nv Жыл бұрын

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

@stephenkaras1523 Жыл бұрын

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 😊

@SaladSharkGaming 9 ай бұрын

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

@zahoorsarbandi2982 Жыл бұрын

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.

@udayteja6595 11 ай бұрын

Removing red was so cool.

@fernandojara6497 6 ай бұрын

Gracias, ya estoy poniendo en practica tus "Best Practicces"!!!!!!👍👍

@Rice0987 Жыл бұрын

And dynamic tip (about reds) is the best!🙋

@thebhaskarjoshi Жыл бұрын

Great tips! Thank you. I always look forward to your new videos.

@danishnawaz7504 Жыл бұрын

You are Amazing Sir.

@Acheiropoietos 2 ай бұрын

❤ very good advice!

@thegaminglearning Жыл бұрын

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.

@karimallahwala7022 Жыл бұрын

Very helpful content, thank you very much

@GoodlyChandeep Жыл бұрын

Glad it was helpful Karim!

@daveboylan247 6 ай бұрын

Kept your goofs (and composure) in your video. Great work here !

@ArmandoReportes Жыл бұрын

I loved the column names tip. So easy and yet so helpful. Thanks.

@semakoadovohekpe2365 Жыл бұрын

Thanks for these tips! Will also review videos on staging queries. 👍🏽👍🏽

@aparnapandravada5073 9 ай бұрын

Awesome tips…

@careerwithDeb Жыл бұрын

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... :)

@williamarthur4801 Жыл бұрын

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.

@GoodlyChandeep Жыл бұрын

Glad it was helpful William!

@Adam_K_W 3 ай бұрын

Great Stuff!

@theg9811 Жыл бұрын

Again a great video all types and levels of users Chandeep... 😀 I can highly recommend these Videos and Courses...

@mathew9665 9 ай бұрын

Nice - I like to use folders for my queries - grouping Parameters, stagging and loading

@odallamico 3 ай бұрын

Excelent tips. Thank you for share them

@JD_JR 9 ай бұрын

A master of your craft. This is so useful. Liked and subbed.

@lucianoriquet8552 Жыл бұрын

Always a pleasure to watch these videos

@maryjavasilyevna861 Жыл бұрын

This is exactly what I needed today!

@ferozromaisa171 8 ай бұрын

Thanks a lot 🙏

@OdelLeal Жыл бұрын

Thank you for share your Best Practices!!! That's great!!!

@GoodlyChandeep Жыл бұрын

Glad you like them Odel!

@MissMicrosoft Жыл бұрын

Really great video, thank you Chandeep!

@GoodlyChandeep Жыл бұрын

Thanks Miss Microsoft !

$@frachiar4444$

@frachiar4444 8 ай бұрын

Very precious lesson!!! Thank you 👍👍

@timestoryx 25 күн бұрын

AWESOME!!!!

@stephenphan2761 11 ай бұрын

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!

@AnbarasuAnnamalai Жыл бұрын

Many useful tips, Thank you.

@GoodlyChandeep Жыл бұрын

Glad it was helpful Anbarasu !

@cristian.angyal Жыл бұрын

"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!

@KamranMumtazAhmed Жыл бұрын

Indians' English vocabularies are way good than those of native speakers. Even though I live in Pakistan but I admit it.

@emilmubarakshin49 Жыл бұрын

Great tips - I’ve been using 1-4 based on your videos. 5 is def welcome addition

@GoodlyChandeep Жыл бұрын

Great to hear Emil!

@punchbuddies220 Жыл бұрын

The best! Cheers man

@GoodlyChandeep Жыл бұрын

Thanks Man!

@vdn5716 Жыл бұрын

This is super awesome...just quick question..

@Timacrafts1 6 ай бұрын

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.

@accountantmahmoudelgamal8005 5 ай бұрын

thank you

@DanKnight 9 ай бұрын

Awesome! Excellent video.

@rubenmunozverdu7528 Жыл бұрын

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?

@GoodlyChandeep Жыл бұрын

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

@jimfitch Жыл бұрын

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.

@Xcellons Жыл бұрын

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.

@freedomdst 3 ай бұрын

the problem with getting column names from files is that in large data the query refresh increases a lot

@JJ_TheGreat Жыл бұрын

This is an excellent video! Great tricks!

@GoodlyChandeep Жыл бұрын

Thanks J.J. !

@scottcha03 Жыл бұрын

Genius! Thank you, well done as always. Love your videos

@GoodlyChandeep Жыл бұрын

Glad you like them Jhon!

@antoniogarrido3058 Жыл бұрын

Thanks Chandeep!! , really useful tips 👍. Avoid the red and quick access toolbar, my favourites.

@GoodlyChandeep Жыл бұрын

Glad you like them Antonio!

@Saeed_Rana 6 ай бұрын

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.

@randomguy-jo1vq Жыл бұрын

I have a deep respect for you

@alializadeh8195 Жыл бұрын

Thanks

@Dev_Bartwal Жыл бұрын

Paaji u r awesome Now you are teaching very clearly I mean not very fast 😅🥰🥰

@raimundojs9547 Жыл бұрын

You're the best! Waiting for your M Language Course.

@GoodlyChandeep Жыл бұрын

Working on it Raimundo!

@oscarmendez-roca9181 Жыл бұрын

Excellent set of tips Chandeep, I suggest to promoved it in pdf quick guides for shared it

@GoodlyChandeep Жыл бұрын

Love the idea

@user-sw6jx4gi1g Ай бұрын

Hello, I'm a Brasilian your Power bi videos helped me with important things. Could you help me? How to create multiply columns in the table using the Advanced Editor?. I created a function and it is called many times, which ends up thinking a little. Each column has its own name. And if you create the dynamic columns, based on a list of names, is it possible?

@zzzzzzzzzzzzzzzz9 Жыл бұрын

Oh, cool, I did not know you could rename steps and add comments. Will make it much easier to navigate

@GoodlyChandeep Жыл бұрын

Have fun!

@alannavarrovaldez3117 Жыл бұрын

Great video! Thanks!

@GoodlyChandeep Жыл бұрын

Glad you liked it Alan!

@FarhanMerchant Жыл бұрын

Great content and insights !

@GoodlyChandeep Жыл бұрын

Glad you think so Farhan!

@michellnatalia5938 Жыл бұрын

Hello Goodly, do you know how to dynamically filter list of date ?

@kaylagnapp5509 Жыл бұрын

Does making the parameter the source hard code it in a way we can’t change the source from the change source settings?

@JordanMurphy2 Жыл бұрын

@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?

@iankr Жыл бұрын

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?

@sharadpunita Жыл бұрын

awesome

@brij26579 Жыл бұрын

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

@juanignacioXI Жыл бұрын

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?

@Hortster Жыл бұрын

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.

@saikumarboga3383 Жыл бұрын

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

@IanDangerfield Жыл бұрын

Ah love the parameter idea

@GoodlyChandeep Жыл бұрын

Awesome Ian !

@davidfamilydoctor9430 Жыл бұрын

What would change for a parameter pointing to a sharepoint folder?

@shwetkumar1719 22 күн бұрын

Can we perform upsert in power query

@munish2839 Жыл бұрын

Its a great idea to fetch the column names from the previous table or steps, Is it suitable for huge dataset?

@GoodlyChandeep Жыл бұрын

Yes.. Should not be a problem

@MrSaxonC. 3 ай бұрын

6:11 how do you let the content of table shows up at the button of window?

@GoodlyChandeep 3 ай бұрын

Don't click on the table click on the side in the empty space and it shows the preview

@VideoAndrega Жыл бұрын

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.

@GoodlyChandeep Жыл бұрын

Why do you need to reposition columns?

@giteshkariya579 Жыл бұрын

@@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.

@GoodlyChandeep Жыл бұрын

@@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 kzfaq.info/get/bejne/jq5jm7p6m9nYeYU.html

@VideoAndrega Жыл бұрын

@@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.

@strangersingh6770 Жыл бұрын

Nice

@GoodlyChandeep Жыл бұрын

Thanks Stranger!

@josevaldesv 5 ай бұрын

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.

@josevaldesv 5 ай бұрын

= SharePoint.Files(Path)

@danishnawaz7504 Жыл бұрын

Plz make videos on SQL

@kesavaraju9652 Жыл бұрын

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.

@deepakkumarpatel6454 Жыл бұрын

In the first point of best practice, is it better to rename the steps without using "space". Any advantage of doing this.

@GoodlyChandeep Жыл бұрын

Referencing the steps becomes neater. = #"Changed Type" versus = ChangedType

@deepakkumarpatel6454 Жыл бұрын

@@GoodlyChandeep 🙋🏻

@stretch233 Жыл бұрын

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.