As you get more and more comfortable with Power BI, you’ll want to explore some of the more advanced features. One of those features is the Advanced Editor, which shows you what’s happening behind the scenes as you use Power Query Editor.
In the past, Power BI only allowed viewing in Advanced Editor. It did not allow for modifications. Now, however, thanks to a fairly recent update, you can make changes to your queries.
If you’re comfortable with coding or ready to get comfortable with coding, Advanced Editor can help you gain a deeper Power BI experience. Here’s more of what you need to know.
Power Query Editor Overview
Advanced Editor is a function of Power Query Editor, so to understand Advanced Editor, you have to get to know Power Query Editor first.
What is Power Query Editor?
Power Query Editor is essentially a container for your data. It’s a component of Power BI Desktop that lets you edit your data and arrange it into the format that works best for your reporting needs.
How to Use Power Query Editor
Before you can start using Advanced Editor, you’ll have to be able to find it in Power Query Editor, and to do that, of course, you’ll have to be able to use and access Power Query Editor in the first place. Here’s what you’ll need to do:
- Open the “Home” tab of Power BI.
- Click on “Edit Queries.”
- Connect Power BI to the data source with which you want to work. Power Query Editor will then load information about that data. You’ll see that data as a set of rows and columns.
From there, you can use the available tabs to work with your data.
- The “Transform” tab lets you add and remove columns and perform similar data transformation tasks.
- The “Add Column” tab lets you add columns and perform more specific tasks related to your new columns.
- The “View” tab lets you display various views.
The “View” tab is also where you’ll see the Advanced Editor option.
Here’s Where Advanced Editor Comes In
Now that you’ve found Advanced Editor, you can get to know it a little better. Advanced Editor gives you a look at the inner workings of your data. Experienced Power BI users can use Advanced Editor to get a closer look at their data and to take their data transformations a step further than they might otherwise be able to do.
What is Advanced Editor?
Advanced Editor lets you see the code that you’re creating with Power Query Editor at each step of the editing process. At any given time while you edit, you can click on Advanced Editor and watch the code update. Advanced Editor uses Power Query Formula language, otherwise called Power Query M language or simply “M.”
Reasons to Use Advanced Editor
Not everyone will need to use Advanced Editor, but those who do want to use it can gain a few benefits from the experience.
Advanced Editor lets you create your own shaping code if necessary, which means that you get added control over your data presentations. Furthermore, even if you don’t make any changes within Advanced Editor, you can still use it to check the syntax of a query and make sure that your queries are free from errors.
Understanding Power Query Formulas
Of course, if you are going to make any changes, you’ll have to understand the code for Power Query formulas.
Power Query M language is similar to F#. If you’re already familiar with F#, you should be able to pick up on M fairly quickly. However, if you’re not familiar with F#, it may take some extra time and practice for you to learn M.
Microsoft provides some tips and tutorials for those who want to get started with M. If you do learn M, you can get some use out of it with other Microsoft products, too.
Using Advanced Editor to Modify Data Source Queries
When you click on “Get Data,” Power BI immediately starts generating a query in the background and will continue to do so without interference. For most purposes, this automated query generation is enough for report creators. Generally, you won’t need to modify anything, although, again, Advanced Editor is helpful for seeing the code in action.
Having said that, if you do need to modify any queries, Advanced Editor is where you’ll do it. Here’s how you can make those modifications:
- Connect to Power BI Advanced Editor using the steps outlined above.
- Click on the query entity that you want to modify in the Advanced Editor window.
- Make your query Changes using the Query Formula.
- Click on “Done.”
- Click on “Close and Apply.”
Using Advanced Editor With Power BI Reports Scheduler Software
Those who use features like Advanced Editor are the kind of people who want to get the most out of Power BI. They’ll leave no feature unexplored as they get to know their options and take their reports to the next level.
People use Power BI Reports Scheduler Software for the same reasons. PBRS from ChristianSteven Software lets users maximize their Power BI usage. With PBRS, users can get the most from everything that Power BI has to offer.
PBRS allows for unlimited reports with just one Power BI license. Even better, it makes those reports simple and automated so that you as a creator can save time and effort. You can even customize your formatting so that each team member gets the exact format and information that they need.
Contact ChristianSteven to Get Started
Ready to start getting more out of your Power BI usage? ChristianSteven Software can make that happen. Don’t just take our word for it, though. Try PBRS for free today to see how it works for yourself. You can also schedule your free live demo so that one of our team members can walk you through the software.
Have questions? Contact the ChristianSteven Software team today. We’d love to hear from you, and we’d love to help you make the most of your Power BI reporting.