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.
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.
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 + /
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 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.🤔
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.
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
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... :)
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.
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!
@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?
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?
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.
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.
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?
"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!
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 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
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 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.
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
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
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.
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.
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
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!
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 + /
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!
You are Amazing Sir.
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.🤔
Gracias, ya estoy poniendo en practica tus "Best Practicces"!!!!!!👍👍
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
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.
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
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.
And dynamic tip (about reds) is the best!🙋
Kept your goofs (and composure) in your video. Great work here !
Very precious lesson!!! Thank you 👍👍
Thanks for these tips! Will also review videos on staging queries. 👍🏽👍🏽
I loved the column names tip. So easy and yet so helpful. Thanks.
Again a great video all types and levels of users Chandeep... 😀 I can highly recommend these Videos and Courses...
Excelent tips. Thank you for share them
This is super awesome...just quick question..
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 😊
Nice - I like to use folders for my queries - grouping Parameters, stagging and loading
Great tips! Thank you. I always look forward to your new videos.
Removing red was so cool.
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... :)
Thank you! I wondered how I could add comments to the renamed steps. Now I know!
Very helpful content, thank you very much
Glad it was helpful Karim!
Many useful tips, Thank you.
Glad it was helpful Anbarasu !
Thank you for share your Best Practices!!! That's great!!!
Glad you like them Odel!
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.
Paaji u r awesome
Now you are teaching very clearly
I mean not very fast 😅🥰🥰
Always a pleasure to watch these videos
This is exactly what I needed today!
A master of your craft. This is so useful. Liked and subbed.
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!
Thanks a lot 🙏
Awesome! Excellent video.
❤ very good advice!
Great tips - I’ve been using 1-4 based on your videos. 5 is def welcome addition
Great to hear Emil!
Awesome tips…
Hello Goodly, do you know how to dynamically filter list of date ?
Really great video, thank you Chandeep!
Thanks Miss Microsoft !
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 Stuff!
Thanks Chandeep!! , really useful tips 👍. Avoid the red and quick access toolbar, my favourites.
Glad you like them Antonio!
You're the best! Waiting for your M Language Course.
Working on it Raimundo!
Genius! Thank you, well done as always. Love your videos
Glad you like them Jhon!
Excellent set of tips Chandeep, I suggest to promoved it in pdf quick guides for shared it
Love the idea
thank you
I have a deep respect for you
Great video! Thanks!
Glad you liked it Alan!
@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?
AWESOME!!!!
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?
This is an excellent video! Great tricks!
Thanks J.J. !
Great content and insights !
Glad you think so Farhan!
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.
Does making the parameter the source hard code it in a way we can’t change the source from the change source settings?
Thanks
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.
The best! Cheers man
Thanks Man!
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?
"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.
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
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.
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.
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
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
Ah love the parameter idea
Awesome Ian !
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.
awesome
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?
Can we perform upsert in power query
Plz make videos on SQL
Hi Chadeep. Where can I find your training courses for Power Query AND Power BI in general?
goodly.co.in/products/
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
Oh, cool, I did not know you could rename steps and add comments. Will make it much easier to navigate
Have fun!
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 🙋🏻
Nice
Thanks Stranger!
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)
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
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.😂