5 Best Practices in Power Query

Подписаться 89 тыс.

Просмотров 47 тыс.

50% 1

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!

Наука

Опубликовано:

23 мар 2023

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 129

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

@swilson1958 5 месяцев назад

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

@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

@ArmandoReportes Год назад

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

@EricHartwigConsulting Год назад

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

@fernandojara6497 4 месяца назад

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

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

@bhaskaraggarwal8971 Год назад

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

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

@thebhaskarjoshi Год назад

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

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

@udayteja6595 9 месяцев назад

Removing red was so cool.

$@frachiar4444$

@frachiar4444 7 месяцев назад

Very precious lesson!!! Thank you 👍👍

@odallamico Месяц назад

Excelent tips. Thank you for share them

@maryjavasilyevna861 10 месяцев назад

This is exactly what I needed today!

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

@franciscom.paredesarias2356 Год назад

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

@GoodlyChandeep Год назад

Thanks Francisco!

@danishnawaz7504 Год назад

You are Amazing Sir.

@semakoadovohekpe2365 Год назад

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

@DanKnight 7 месяцев назад

Awesome! Excellent video.

@lucianoriquet8552 Год назад

Always a pleasure to watch these videos

@daveboylan247 4 месяца назад

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

@Rice0987 Год назад

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

@vdn5716 Год назад

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

@Acheiropoietos 21 день назад

❤ very good advice!

@databicep Год назад

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

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

@OdelLeal Год назад

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

@GoodlyChandeep Год назад

Glad you like them Odel!

@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

@scottcha03 Год назад

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

@GoodlyChandeep Год назад

Glad you like them Jhon!

@SaladSharkGaming 7 месяцев назад

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

@aparnapandravada5073 7 месяцев назад

Awesome tips…

@Donkeys_Dad_Adam Месяц назад

Great Stuff!

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

@JD_JR 8 месяцев назад

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

@theg9811 Год назад

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

@ferozromaisa171 6 месяцев назад

Thanks a lot 🙏

@JJ_TheGreat Год назад

This is an excellent video! Great tricks!

@GoodlyChandeep Год назад

Thanks J.J. !

@karimallahwala7022 Год назад

Very helpful content, thank you very much

@GoodlyChandeep Год назад

Glad it was helpful Karim!

@alannavarrovaldez3117 Год назад

Great video! Thanks!

@GoodlyChandeep Год назад

Glad you liked it Alan!

@randomguy-jo1vq Год назад

I have a deep respect for you

@AnbarasuAnnamalai Год назад

Many useful tips, Thank you.

@GoodlyChandeep Год назад

Glad it was helpful Anbarasu !

@mathew9665 7 месяцев назад

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

@accountantmahmoudelgamal8005 4 месяца назад

thank you

@MissMicrosoft Год назад

Really great video, thank you Chandeep!

@GoodlyChandeep Год назад

Thanks Miss Microsoft !

@emilmubarakshin49 Год назад

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

@GoodlyChandeep Год назад

Great to hear Emil!

@FarhanMerchant Год назад

Great content and insights !

@GoodlyChandeep Год назад

Glad you think so Farhan!

@alializadeh8195 Год назад

Thanks

@raimundojs9547 Год назад

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

@GoodlyChandeep Год назад

Working on it Raimundo!

@punchbuddies220 Год назад

The best! Cheers man

@GoodlyChandeep Год назад

Thanks Man!

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

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

@freedomdst Месяц назад

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

@IanDangerfield Год назад

Ah love the parameter idea

@GoodlyChandeep Год назад

Awesome Ian !

@sharadpunita Год назад

awesome

@stephenphan2761 9 месяцев назад

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!

@michellnatalia5938 Год назад

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

@Dev_Bartwal Год назад

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

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

@Timacrafts1 5 месяцев назад

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.

@antoniogarrido3058 Год назад

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

@GoodlyChandeep Год назад

Glad you like them Antonio!

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

@kaylagnapp5509 Год назад

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

@oscarmendez-roca9181 Год назад

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

@GoodlyChandeep Год назад

Love the idea

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

@zzzzzzzzzzzzzzzz9 Год назад

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

@GoodlyChandeep Год назад

Have fun!

@Saeed_Rana 5 месяцев назад

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.

@danishnawaz7504 Год назад

Plz make videos on SQL

@davidfamilydoctor9430 Год назад

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

@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

@brij26579 10 месяцев назад

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.

@strangersingh6770 Год назад

Nice

@GoodlyChandeep Год назад

Thanks Stranger!

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

@MrSaxonC. Месяц назад

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

@GoodlyChandeep Месяц назад

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

@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

@santhoshmurali7682 Год назад

👍

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-XH3hWI6stIQ.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.

@geineralbertomejiagarzon4034 Год назад

Hi Chadeep. Where can I find your training courses for Power Query AND Power BI in general?

@GoodlyChandeep Год назад

goodly.co.in/products/

@josevaldesv 4 месяца назад

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 4 месяца назад

= SharePoint.Files(Path)

@hashi856 Год назад

You said you would leave links to videos about staging and extracting intermediate steps

@GoodlyChandeep Год назад

Sorry I missed it. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-weafpG2yG1w.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--xhnpar6fmw.html

@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 🙋🏻

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