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.
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
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 + /
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.
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 !
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
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.🤔
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.
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.
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!
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 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... :)
"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!
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.
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?
Summary: The video lists down five best practices to follow in Power Query to make your queries more robust. 1. Name the steps that you create in power query: This will help you understand the transformation that has been done in the query. 2. Write comments or notes for complicated data Transformations: This will help other users understand the Transformations that you have applied. 3. Notice and omit the red: Red in the M code typically indicates hard-coded inputs, which can make your query break if the source data changes. Try to minimize the amount of red in your query by using formulas instead of hard-coded values. 4. Parameterize and Stage the queries: This will help you reuse the work that you have done in one query to support other queries. 5. Use the quick access toolbar: This will help you write the queries faster. Key points: The video lists down five best practices to follow in Power Query to make your queries more robust. 1. Name the steps that you create in Power Query. This will help you understand the transformation that has been done in the query. 2. Write comments or notes to explain complicated data Transformations. This will help other users understand the query. 3. Notice and omit the red in the M code. Red indicates hard-coded inputs, which can break the query if the source data changes. 4. Parameterize and stage the queries that you're creating. This will make your queries more reusable and efficient. 5. Use the quick access toolbar in Power Query. This will help you write queries faster. Examples discussed: The video discusses the following examples in Power Query: 1. A query with generic step names like "Custom 1", "Custom 2", etc. This makes it difficult to understand the transformations that have been applied to the data. 2. A query with renamed steps that clearly describe the transformations performed, such as "Source Data", "Keep All Files with Year Label", "Convert Excel Files to Table", etc. 3. A query with a step that converts Excel files to tables and only keeps the first row of data. This step has a comment explaining that only the first sheet of the Excel file will be processed. 4. A query with hard-coded column names. These values are susceptible to break the query if the source data changes. 5. A query with parameterized and staged queries. This means that the data is only collected once and then fed into all the queries that need it. 6. A query with a quick access toolbar that contains frequently used commands like "Advanced Editor" and "Custom Column". This can help you write queries faster. All steps that speaker talked about: The speaker talked about the following best practices in Power Query: 1. **Name the steps that you create in Power Query.** This will make your queries more readable and easier to understand. 2. **Write comments or notes to explain complicated data Transformations.** This will help other users understand your queries and make them more maintainable. 3. **Notice and omit the red in the M code.** Red indicates hard-coded inputs, which can make your queries more fragile. Try to use formulas instead of hard-coded values. 4. **Parameterize and Stage the queries that you're creating.** This will make your queries more flexible and reusable. 5. **Use the quick access toolbar in Power Query.** This will help you write queries faster. The speaker also mentioned that it is important to use clear and concise naming conventions for your steps. For example, instead of using generic names like "Custom 1" and "Custom 2", you could use names like "Add Year Column" and "Group by Year and Customer". Additionally, the speaker recommended using comments to explain any complex logic or calculations that you are performing in your queries. This will help other users understand your code and make it easier to maintain. googleusercontent.com/youtube_content/4
@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?
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?
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
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.
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
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 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 ua-cam.com/video/XH3hWI6stIQ/v-deo.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.
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.
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.
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
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 + /
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.
So helpful! Especially re-naming steps. Don't know why I never thought to do this. Thanks!
Superb, as usual.✨Apart from your content, I would also like to appreciate your lighting. Very soothing.
Very awesome video! I love it when a fellow Excel & Power Query enthusiast shares their best practices!
Thank you for creating/sharing this video!
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
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 !
Thank you very much for these Tips that are really important to have everything more organized.
Greetings from Chile
Thanks Francisco!
Gracias, ya estoy poniendo en practica tus "Best Practicces"!!!!!!👍👍
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
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.🤔
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.
You are Amazing Sir.
Removing red was so cool.
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.
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.
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!
And dynamic tip (about reds) is the best!🙋
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 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... :)
Kept your goofs (and composure) in your video. Great work here !
I loved the column names tip. So easy and yet so helpful. Thanks.
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.
"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.
Thank you! I wondered how I could add comments to the renamed steps. Now I know!
Thanks for these tips! Will also review videos on staging queries. 👍🏽👍🏽
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!
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.
Very precious lesson!!! Thank you 👍👍
Nice - I like to use folders for my queries - grouping Parameters, stagging and loading
Again a great video all types and levels of users Chandeep... 😀 I can highly recommend these Videos and Courses...
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?
Summary:
The video lists down five best practices to follow in Power Query to make your queries more robust.
1. Name the steps that you create in power query: This will help you understand the transformation that has been done in the query.
2. Write comments or notes for complicated data Transformations: This will help other users understand the Transformations that you have applied.
3. Notice and omit the red: Red in the M code typically indicates hard-coded inputs, which can make your query break if the source data changes. Try to minimize the amount of red in your query by using formulas instead of hard-coded values.
4. Parameterize and Stage the queries: This will help you reuse the work that you have done in one query to support other queries.
5. Use the quick access toolbar: This will help you write the queries faster.
Key points:
The video lists down five best practices to follow in Power Query to make your queries more robust.
1. Name the steps that you create in Power Query. This will help you understand the transformation that has been done in the query.
2. Write comments or notes to explain complicated data Transformations. This will help other users understand the query.
3. Notice and omit the red in the M code. Red indicates hard-coded inputs, which can break the query if the source data changes.
4. Parameterize and stage the queries that you're creating. This will make your queries more reusable and efficient.
5. Use the quick access toolbar in Power Query. This will help you write queries faster.
Examples discussed:
The video discusses the following examples in Power Query:
1. A query with generic step names like "Custom 1", "Custom 2", etc. This makes it difficult to understand the transformations that have been applied to the data.
2. A query with renamed steps that clearly describe the transformations performed, such as "Source Data", "Keep All Files with Year Label", "Convert Excel Files to Table", etc.
3. A query with a step that converts Excel files to tables and only keeps the first row of data. This step has a comment explaining that only the first sheet of the Excel file will be processed.
4. A query with hard-coded column names. These values are susceptible to break the query if the source data changes.
5. A query with parameterized and staged queries. This means that the data is only collected once and then fed into all the queries that need it.
6. A query with a quick access toolbar that contains frequently used commands like "Advanced Editor" and "Custom Column". This can help you write queries faster.
All steps that speaker talked about:
The speaker talked about the following best practices in Power Query:
1. **Name the steps that you create in Power Query.** This will make your queries more readable and easier to understand.
2. **Write comments or notes to explain complicated data Transformations.** This will help other users understand your queries and make them more maintainable.
3. **Notice and omit the red in the M code.** Red indicates hard-coded inputs, which can make your queries more fragile. Try to use formulas instead of hard-coded values.
4. **Parameterize and Stage the queries that you're creating.** This will make your queries more flexible and reusable.
5. **Use the quick access toolbar in Power Query.** This will help you write queries faster.
The speaker also mentioned that it is important to use clear and concise naming conventions for your steps. For example, instead of using generic names like "Custom 1" and "Custom 2", you could use names like "Add Year Column" and "Group by Year and Customer".
Additionally, the speaker recommended using comments to explain any complex logic or calculations that you are performing in your queries. This will help other users understand your code and make it easier to maintain.
googleusercontent.com/youtube_content/4
Always a pleasure to watch these videos
This is exactly what I needed today!
Paaji u r awesome
Now you are teaching very clearly
I mean not very fast 😅🥰🥰
@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?
Hello Goodly, do you know how to dynamically filter list of date ?
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?
Thank you for share your Best Practices!!! That's great!!!
Glad you like them Odel!
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
I have a deep respect for you
Does making the parameter the source hard code it in a way we can’t change the source from the change source settings?
Great tips! Thank you. I always look forward to your new videos.
Great tips - I’ve been using 1-4 based on your videos. 5 is def welcome addition
Great to hear Emil!
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.
Excellent set of tips Chandeep, I suggest to promoved it in pdf quick guides for shared it
Love the idea
Excelent tips. Thank you for share them
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
You're the best! Waiting for your M Language Course.
Working on it Raimundo!
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.
This is super awesome...just quick question..
Very helpful content, thank you very much
Glad it was helpful Karim!
Many useful tips, Thank you.
Glad it was helpful Anbarasu !
A master of your craft. This is so useful. Liked and subbed.
Thanks Chandeep!! , really useful tips 👍. Avoid the red and quick access toolbar, my favourites.
Glad you like them Antonio!
❤ very good advice!
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
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?
Awesome! Excellent video.
Genius! Thank you, well done as always. Love your videos
Glad you like them Jhon!
Can we perform upsert in power query
Really great video, thank you Chandeep!
Thanks Miss Microsoft !
Thanks a lot 🙏
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 ua-cam.com/video/XH3hWI6stIQ/v-deo.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.
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 🙋🏻
Great Stuff!
Awesome tips…
Hi Chadeep. Where can I find your training courses for Power Query AND Power BI in general?
goodly.co.in/products/
This is an excellent video! Great tricks!
Thanks J.J. !
thank you
AWESOME!!!!
Great video! Thanks!
Glad you liked it Alan!
Ah love the parameter idea
Awesome Ian !
Great content and insights !
Glad you think so Farhan!
The best! Cheers man
Thanks Man!
Oh, cool, I did not know you could rename steps and add comments. Will make it much easier to navigate
Have fun!
Thanks
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
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
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)
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.
Plz make videos on SQL
You said you would leave links to videos about staging and extracting intermediate steps
Sorry I missed it.
ua-cam.com/video/weafpG2yG1w/v-deo.html
ua-cam.com/video/-xhnpar6fmw/v-deo.html
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.
awesome
Nice
Thanks Stranger!
I leave default step names cause it will be hard for aliens to read my queries.😂
👍