648 Optimizing Performance
The query generated to extract the value from the Excel file looks like this:
let
Source = Excel.Workbook(File.Contents(
"C:\Data\ExcelParameterFile.xlsx"), null, true),
ParameterTable_Table =
Source{[Item="ParameterTable",Kind="Table"]}[Data],
URL = ParameterTable_Table{0}[URL]
in
URL
Rename this query myExcelURL. Do you recall the earlier situation where we used myURLParameter?
What if we replace that with myExcelURL, the parameter from our Excel file? Try using this in a query:
Web.BrowserContents( myExcelURL )
After making this change, you’ll encounter an error that looks like the following:
Figure 15.36: The Formula Firewall error when referencing queries connecting to a data source
Why does this error arise, especially when we successfully used a parameter in the previous example?
To understand this, let’s focus on the fundamental principle of the formula firewall: “A partition can
either reference other partitions or access data from sources that have a compatible privacy level. It cannot
do both at the same time.”
In our initial example, there were two reasons for the absence of an error. Firstly, the myURLParameter
was a native, hardcoded parameter that did not access any external data sources. Secondly, the partitioning
process excluded the native parameter from its scope, meaning its reference was not considered
as a reference to another partition.
The situation changes with a parameter derived from an Excel file. Such a parameter accesses data
from another source (the Excel file), and thus, it’s included in the partitioning process. When the
Web.BrowserContents function attempts to access a website (PowerQuery.How) and simultaneously
references a partition (the Excel parameter), it breaches the firewall’s fundamental rule.
Partitioning in Power Query can be pretty complicated. Even reading the Microsoft documentation
on it will leave you with a lot of remaining questions. However, the main point to remember is this: if
you reference one query to get data from a source, like our Excel file, and then try to use that data in
another query to access a source, it breaks the rules of the formula firewall. So, what’s the best way
to fix this issue?
Chapter 15 649
Resolving the firewall error
When we attempt to reference the Excel parameter from this different partition, we breach a key firewall
rule. We cannot access a data source (in this case, the web request) and simultaneously reference
another partition (the Excel parameter). Yet, there are plenty of scenarios where you want to be able
to use a data point that is stored elsewhere, in this case, the Excel URL. Our objective is to enable the
query to retrieve the URL from the external source while finding a way to keep the logic within the
same partition. To achieve this, we will explore two possible methods.
Method 1: Using a function
The first method involves storing the myExcelURL parameter logic within a function. A function stores
logic into reusable code but does not execute it. When we then call this function in a query, the accessing
of data happens within the partition where the function is called. This is different from referencing
a query because, in that situation, the accessing of the data occurs in the other query, which will be
seen as a separate partition and potentially triggers the firewall.
To create this function, you can add a simple function definition that refers to the previous step at
the end of your query:
let
Source = Excel.Workbook(File.Contents(
"C:\Data\ExcelParameterFile.xlsx"), null, true),
ParameterTable_Table =
Source{[Item="ParameterTable",Kind="Table"]}[Data],
URL = ParameterTable_Table{0}[URL],
myFunction = () => URL
in
myFunction
This script creates a function. Make sure to name it fxMyExcelURL. Use this function in another query
to make a web request, like this:
Web.BrowserContents( fxMyExcelURL() )
Doing this allows you to make your request successfully without running into the formula firewall.
You can find this solution in the exercise files of this chapter.
Method 2: Integrating the logic into one query
A second approach is integrating all the logic of accessing the Excel file into the same query. Doing so
ensures that all related steps fall within a single partition, which allows using the parameter to access
an external data source under the formula firewall’s rules.
650 Optimizing Performance
To consolidate the steps, you can take the logic of the Excel Workbook query and add a new step at the
end of the query. In that step, you can reference the URL from the Excel workbook as follows:
let
Source = Excel.Workbook(File.Contents(
"C:\Data\ExcelParameterFile.xlsx"), null, true),
ParameterTable_Table =
Source{[Item="ParameterTable",Kind="Table"]}[Data],
myContainedExcelURL = ParameterTable_Table{0}[URL],
HTMLCode = Web.BrowserContents( myContainedExcelURL )
in
HTMLCode
Combining these steps into a single query allows the partitioning process to keep the steps that access
data within the same partition. This approach successfully resolves the first Formula Firewall error.
To see this for yourself, you can find the complete query in this chapter’s exercise files.
By applying the above steps, you end up in the following situation:
Figure 15.37: Prompt for setting privacy levels
The error informs you that it requires additional information about data privacy. If you press Continue
and select Public, your query will return its results. However, by choosing Private or Organizational,
your query may lead to the second type of Formula Firewall error, which we will discuss next.
Firewall error: Accessing compatible data sources
The second firewall error can happen when you combine data sources with incompatible privacy levels.
Remember how the firewall is meant to prevent unintended data leakage? That’s what this error is
about. The message for this error is:
Formula.Firewall: Query 'X' (step 'Y') is accessing data sources with incompatible
privacy levels. Please rebuild this data combination.
Consider the scenarios depicted in the following image:
Figure 15.38: Two queries where one contains data sources that are incompatible
Chapter 15 651
The image shows two queries. Focusing on Query1, it accesses data from two external sources. If the
Web.Contents step tried to reference another partition, it would normally trigger a “referencing other
partitions” error due to simultaneously accessing a data source and referencing another partition.
However, that’s not the issue here as the data is within the same query. That allows the partitioning
process to put both steps within the same partition. The real problem for Query1 is a mismatch in
privacy levels: the data source MyExcelURL1 is marked as ‘Private, while the Web.Contents step is set
to the Organizational privacy level. Because these levels don’t match, Power Query reports the second
firewall error for accessing incompatible data sources.
Query2 doesn’t encounter this error because both steps in the query involve data sources with matching
privacy levels. Therefore, the formula firewall allows both data sources to combine.
Understanding this might seem complex, but don’t worry. We’ll break down this example further
with a practical case. To grasp the error message fully, it’s essential to first learn about privacy levels.
Understanding privacy levels
In Power Query, each data source can be assigned a privacy level. These levels are important for determining
how data from different sources can be combined.
Here’s an overview of the privacy levels and their effects on query folding:
• Public: Data from a Public source can be freely combined with other data sources.
• Organizational: This setting allows data to be combined only with other Organizational sources.
• Private: The most restrictive setting. Private data does not combine with other sources, maintaining
strict isolation.
Think of privacy levels like this: if you label a data source as Private, it won’t mix with other data in
queries. This keeps sensitive information safe and separate. Data marked as Organizational can only
be mixed with other Organizational data. Public data offers the most flexibility as it can easily be
combined with any other data source that permits it. This typically includes data sources with their
privacy levels set to Public or None.
Setting privacy levels
When you attempt to combine a data source without a set privacy level, Power Query prompts you to
choose one. To set privacy levels manually:
1. In Power BI Desktop:
• Navigate to the Home tab on the Power Query ribbon and select Data Source Settings.
• Choose the data source to modify and click Edit Permissions to set its privacy level.
652 Optimizing Performance
Figure 15.39: Setting privacy levels in Data source settings
2. In the Power BI service:
• Access settings via the top-right gear icon, then select Manage Connections and Gateways.
• Find your data source and set the Privacy Level at the bottom of the screen.
So, what does this have to do with the formula firewall?
Resolving the firewall error
Let’s revisit our previous example where we retrieved a URL from an Excel file. This action prompted
an error message requesting privacy-level information for the data sources:
Figure 15.40: Error message requesting information on privacy levels
The above message requires information about the data privacy level of the web request. To resolve
this error, we perform one of two methods.
Method 1: Setting compatible privacy levels
Whether or not we receive the Formula Firewall error depends on the privacy level settings:
• Public: Your query will execute immediately unless the Excel file we connect to has its privacy
level set to Private or Organizational, which causes a firewall error. The privacy levels None
and Public are both compatible.
Chapter 15 653
• Organizational: Setting the query to Organizational prompts a check of the Excel file’s privacy
level. The query runs smoothly if the file we combine with is set to Organizational. Any other
privacy level will trigger the Formula Firewall error.
• Private: Selecting Private invariably leads to a firewall error.
Let’s say we want to set the privacy level to Organizational. We encounter a firewall error when selecting
Continue and assigning Organizational to our data source. This happens because, while we
set the web request to Organizational, the privacy level for the Excel file connection is still undefined.
To resolve this:
1. Navigate to Data Source Settings.
2. Locate the Excel file under Data sources in current file.
3. Use Edit Permissions to set the Excel file’s privacy level to Organizational.
4. Confirm your choice, then return to your query.
5. Click Refresh Preview to update your queries.
After these steps, your queries should function without privacy-level-related errors.
Method 2: Ignoring privacy levels
Another method to prevent the Formula Firewall error is to ignore the privacy level settings. The
formula firewall’s role as a gatekeeper between your data sources can slow down query performance.
This feature is important for preventing data leaks, especially in query folding scenarios. However,
you have an alternative if you’re dealing with non-sensitive data.
In Power BI Desktop, you can choose to ignore privacy levels. This may also help to improve the performance
of your queries as any compatibility checks can be skipped.
In situations like our previous example, where you’re asked to set privacy levels, continuing without
setting them triggers a specific pop-up message:
Figure 15.41: Privacy levels notification – option to ignore privacy levels
You have the choice to select Ignore Privacy Levels. Opting for this allows your query to execute
without running into privacy-related Formula Firewall errors.
654 Optimizing Performance
To find this setting:
• Navigate to File, then Options and Settings, and click Options.
• Select Always ignore Privacy Level settings in the Privacy section.
That looks as follows:
Figure 15.42: The Options menu to configure Privacy Levels
To conclude, this chapter has demonstrated how to address various Formula Firewall errors using
specific strategies. We solved the partition referencing error by consolidating logic within a single
query and creating a custom function. For the data privacy error, we either aligned data privacy levels
or ignored privacy settings entirely.
However, it’s important to note that the examples provided in this chapter do not support refreshing
in the Power BI service. This limitation arises because the example involves fetching a URL in one
query and using it in another. Power Query’s engine requires that the base URL of a web request is
explicitly visible to the M engine. Our dynamic referencing of the URL obscures this, leading to a
dynamic queries error.
Despite this specific limitation for a web request, the techniques outlined in this chapter remain effective
for resolving Formula Firewall situations. For instance, if these methods were applied to filter
a database query, the query would successfully refresh in the Power BI service.
So far, you’ve learned how to deal with the formula firewall and what you can do to ensure query
folding takes place for your queries. In the next section, we’ll look at other strategies to improve the
performance of our queries.
Important: Using this setting might expose sensitive or confidential data. Additionally,
this setting is only applicable in Power BI Desktop. The Power BI service does not respect
this setting, and you’ll need to configure the appropriate privacy levels.
Chapter 15 655
Optimizing query performance
Power Query operates within a constrained environment. Specifically, each mashup container has a
limited amount of resources. This limitation is an important factor to consider when creating queries.
It is important to reduce the data volume right at the beginning of your query for fast queries. By doing
so, you not only speed up the query process but also prevent exceeding resource limits that cause
paging and can lead to slow performance or failures.
As previously discussed, using the query folding mechanism is one of the most effective strategies
for optimizing performance. However, there are scenarios where your data source does not support
query folding, or a required transformation breaks the folding process. In such cases, the strategy
shifts toward minimizing the memory footprint of your query. So, what are some effective methods
to achieve this?
Prioritize filtering rows and removing columns
It’s worth emphasizing here that one of the first steps you should focus on is reducing your dataset to
keep only what is necessary. You can do that by:
• Filtering rows: As early as possible in your query, prioritize filtering your rows to include only
the necessary ones. By reducing the number of rows, you decrease the amount of data that
Power Query needs to keep in memory, leading to faster processing and less memory usage.
• Removing unnecessary columns: Like filtering rows, removing unnecessary columns is equally
important. This practice trims the data volume and simplifies your data model, making it
easier to work with and understand.
Both operations make sense from a computing perspective. But besides these, there’s another distinction
to make for the type of operations to perform first.
Buffering versus streaming operations
Performing operations at the data source is often faster than doing them locally. However, when performing
operations locally, you can find two types of operations: buffering and streaming. So, what
are these operations, and what is their relationship to performance?
Buffering operations
Buffering operations are those that require reading the entire dataset to produce a result. These operations
are resource-intensive and demand a complete scan of the data up to the previous step. This
also means that the amount of memory used is proportional to the size of the input.
Consider an example where we sort a dataset and then retrieve the top 1000 rows. In this case, the
whole dataset is needed to determine the top 1000 rows accurately, even if you only want to return a
small part of the data. On the other hand, if you first select the top 1000 rows and then sort them, the
sorting process only needs to keep these 1000 rows in memory. It can do that by simply retrieving the
top 1000 rows, never requiring the full dataset. In this scenario, the sorting is considered a buffering
operation.
656 Optimizing Performance
Buffering operations are not limited to sorting data. They also include certain types of joins, grouping
data, and altering the structure of your table, such as through pivot or transpose operations. Likewise,
operations that return unique values or add a ranking column require scanning the entire table.
These are the types of operations that use a lot of resources and should be limited when optimizing
performance.
Streaming operations
In contrast to buffering operations, streaming operations do not require scanning the entire dataset to
return a result. A good example of this is the filtering operation. Filtering a list or table processes data
in a streaming manner. As data flows through, Power Query evaluates and returns results progressively.
This means that for a preview screen in the Power Query Editor, only a portion of the data is needed to
fill the top 1000 rows. This also directly leads to a more responsive Power Query Editor as it can show
your data previews quicker. Typical streaming operations include adding columns, filtering rows, and
selecting columns. Objects that support streaming include those of the table, list, and binary type.
Maximizing streaming transformations helps reduce memory consumption and avoid performance
issues when running a query. Knowing this, you may wonder how to identify whether you’re working
with a streaming or buffering operation. The answer is that you can determine this in the new Power
Query interface by using the query plan.
Using the query plan
The query plan is a feature in the new Power Query experience that is meant to provide more information
about the evaluation of your query. Whenever you want to know more details on the evaluation
of a particular step, right-click on the step and select Query Plan.
For instance, let’s look at a simple query:
Figure 15.43: A database query with a range of transformations
This query connects to an SQL database, selects some columns, adds an index column, filters the top
20 rows, and sorts the table.
To analyze the query plan for the SortRows step, right-click on the step and select View query plan:
Chapter 15 657
Figure 15.44: Right-click on a step to select View query plan
This action will open a window displaying the query plan:
Figure 15.45: Query plan indicates how a step is evaluated
The query plan visualizes the evaluation path of your query. It begins on the right, showing the connection
to the SQL database, and progresses to the left, leading to the final step of sorting the table.
Within the plan, you can encounter three types of indicators for each step:
• Remote: Steps marked as Remote indicate operations performed at the data source. When a
step folds, you can view the native query by selecting View details.
• Streaming: These steps do not require scanning all the data to return a result. They process
the data as it streams through, retrieving only the rows needed to populate the preview.
• Buffering: Steps with this indicator require a full scan of the data from the previous step.
The query plan not only reveals whether an operation is buffering or streaming but also shows which
steps are folded and the underlying queries at the data source. What’s good to remember about memory
is that when (part of) your query does not fold, operations from that point are performed in memory
of the Power Query engine.
658 Optimizing Performance
You just learned about the types of operations that require either a partial or full scan of your dataset.
Another strategy to improve your query performance is by storing parts of your query in memory
before applying an expensive operation. This can be done by using buffer functions, which we will
discuss next.
Using buffer functions
Buffer functions in the M language, identified by the suffix .Buffer, play an important role in improving
the performance of Power Query operations. They temporarily save data in your computer’s memory.
This process, called buffering, can speed up your queries but also has some limitations.
Buffer functions are most useful in scenarios where Power Query may execute multiple requests to
fetch the same data from an external source. This situation can happen during complex operations
that require accessing data repeatedly, such as in detailed row-by-row transformations. You also see
this when using a replacement table to perform multiple replacements.
Since repeatedly requesting the same data can be slow, especially from an external source, you may
consider buffering the data into memory. In that way, the data is only retrieved once and can be reused
from memory. Another scenario where you benefit from the buffer function is when computing a running
total. The performance improves considerably when you buffer the values for the running total.
On the other hand, if you buffer a large amount of data in memory and surpass the memory limit
of your mashup container, the system starts moving data between RAM and storage (paging). This
slows down your queries due to the slower access speed compared to RAM. To ensure your queries
are fast, it’s therefore important to consider your query’s memory footprint. In the next section, we’ll
look at different approaches to creating a running total and how buffer functions help improve the
query performance.
The impact of buffering and running totals
In this section, we will use an example to measure the performance and memory consumption of
different approaches. Unfortunately, Power Query has no easy built-in way to measure its performance.
Whereas query diagnostics are available in Power BI Desktop, the feature makes it hard to interpret
its metrics.
For a more accurate measurement of query speed, we have used a tool called SQL-profiler and connected
it to our Power BI dataset. We have computed our refresh times by refreshing each query separately
three times and taking the average refresh time. Let’s see what our setup looks like.
Optimization Tip: Prioritize query folding where possible. After folding is no longer feasible,
use streaming operations before buffering. This approach ensures your data quickly
populates the preview data, reducing wait times while developing. Only use buffering operations
when necessary to complete your queries. Strategically sequencing these operations
can significantly reduce the time it takes for a preview to load when modifying your query.
Chapter 15 659
The setup
Consider a dataset containing parking transactions, each with an associated payment amount:
Figure 15.46: Sample parking transactions dataset
You can follow along with this example by using the exercise files provided on the GitHub page of
this book.
Our goal is to calculate a cumulative total of these payments. To calculate the running total, we use
two functions: List.Sum and List.FirstN. List.FirstN selects values up to a particular row, and
List.Sum adds these values to give a cumulative total. We begin by adding an index column starting
at 1, increasing by 1 for each row. This forms the basis of our query:
let
Source = myCSV,
AddedIndex =
Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
in
AddedIndex
Next, we’ll explore four methods to compute a running total:
1. From regular table
2. From buffered table
3. From buffered column
4. From buffered column and using List.Generate
Each method offers an alternative approach to calculating the running total, potentially affecting the
efficiency and performance of the process.
Method 1: Running total from regular table
For the first method, we will add a new column with the following expression:
List.Sum( List.FirstN( AddedIndex[Amount Paid], [Index] ))
660 Optimizing Performance
This formula calculates the running total for each transaction:
Figure 15.47: Adding a running total without buffering values
When evaluating the performance of this method in Power BI, we observe:
• Processing time: It takes an average of 128 seconds.
• Data retrieved: The method retrieves 1.88 GB of data.
Given that the original data file is only 393 KB, the substantial amount of data processed suggests that
Power Query reads the file multiple times. This inefficiency is a significant downside of using a regular
table for running total calculations.
Method 2: Running total from buffered table
Method 2 introduces a variation in calculating the running total. Instead of using a regular table, this
method involves buffering the table in memory. This is achieved with the Table.Buffer function, as
demonstrated in the following query:
let
Source = myCSV,
AddedIndex =
Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
BufferTable = Table.Buffer( AddedIndex ),
AddRunningTotal = Table.AddColumn(BufferTable, "Running Total",
each List.Sum( List.FirstN( BufferTable[Amount Paid], [Index] )))
in
AddRunningTotal
In this step, Table.Buffer loads the data into memory. The advantage here is that instead of making
multiple requests to the source file, the data is now stored in memory. Subsequent operations on this
table instead can use the in-memory data.
The performance metrics for this method in Power BI Desktop show a notable improvement:
• Processing time: Reduced to an average of 61 seconds.
• Data retrieved: Only 394 KB of data is retrieved.
Chapter 15 661
This approach increases query speed by 52.3% and reduces its data consumption. The total data retrieved
is now in line with the size of the original CSV file. This also shows how effective the Table.Buffer
function is for optimizing performance.
Method 3: Running total from buffered column
Earlier in this chapter, we stressed the importance of minimizing memory usage for the mashup engine.
Following this principle, method 3 buffers only the specific column used in the running total calculation.
This approach reduces the memory footprint and hopefully reduces the query’s refresh time.
The query structure for this method is as follows:
let
Source = myCSV,
AddedIndex =
Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
BufferColumn = List.Buffer( AddedIndex[Amount Paid] ),
AddRunningTotal = Table.AddColumn(AddedIndex, "Running Total",
each List.Sum( List.FirstN( BufferColumn, [Index] )))
in
AddRunningTotal
In this query, the BufferColumn step specifically targets the Amount Paid column for buffering.
The performance results for this method show another significant improvement:
• Processing time: Remarkably reduced to an average of 1.2 seconds.
• Data retrieved: Amounts to 786 KB.
This method achieves a staggering 99.1% reduction in refresh time compared to the original query.
An interesting observation is that the method accesses the CSV file twice—most likely once for the
original table and once to buffer the column used in the running total calculation. This shows that the
amount of data retrieved is not always an indicator of the speed of the query. Instead, this scenario
indicates a strategic balance between memory efficiency and processing speed.
Does this mean there’s nothing left to improve? Well, not exactly. The List.FirstN method for creating
a running total is not the most efficient one. To improve things even further, we can make use of the
List.Generate function.
Method 4: Running total from buffered column using List.Generate
The List.FirstN method, while functional, is not the most efficient for summing values in a running
total calculation. It requires retrieving and adding a range of values for each row. An alternative and
more efficient technique is the use of the List.Generate function. This method computes the running
total by adding each new value to the previously calculated total rather than recalculating the sum over
an ever-increasing range of values each time. For an in-depth discussion about this List.Generate
method, see Chapter 14, but for now, let’s explore this method in the current context.
662 Optimizing Performance
The query using List.Generate is as follows:
let
Source = myCSV,
BufferedColumn = List.Buffer( Source[Amount Paid] ),
RunningTotal =
List.Generate (
() => [ RT = BufferedColumn{0}, RowIndex = 0 ],
each [RowIndex] < List.Count( BufferedColumn ),
each [ RT = List.Sum( { [RT], BufferedColumn{[RowIndex] + 1} }),
RowIndex = [RowIndex] + 1 ],
each [RT] ),
CombineTables = Table.FromColumns(
Table.ToColumns( Source ) & { RunningTotal },
Table.ColumnNames( Source ) & { "Running Total" } )
in
CombineTables
This method still buffers only a single column. The List.Generate function then efficiently computes
the running total. Lastly, the original table columns are combined with the newly created running
total column.
The performance of this revised method in Power BI Desktop is as follows:
• Processing time: Remarkably reduced to an average of 201 milliseconds.
• Data retrieved: Amounts to 786 KB.
This method shows a remarkable 99.8% improvement in processing time compared to the original
approach.
The following table summarizes the performance of these four different methods:
Method Description Processing Time Data Retrieved Improvement
1 List.FirstN + Regular Table 128 seconds 1.88 GB 0.0%
2 List.FirstN + Buffered Table 61 seconds 394 KB 52.3%
3 List.FirstN + Buffered Column 1.2 seconds 786 KB 99.1%
4
List.Generate + Buffered
Column 201 milliseconds 786 KB 99.8%
Table 15.1: Comparing the performance of different methods
As this section showed, buffer functions like List.Buffer and Table.Buffer can significantly impact
the memory used to perform calculations in M, especially when your query accesses your data source
multiple times. You can try one of these functions whenever you notice your query using much more
memory than required. While this is an excellent optimization, don’t forget to look for alternative
approaches that make more efficient use of memory, just like List.Generate in this example.
Chapter 15 663
The performance of your queries not only depends on how you structure your query. An important
consideration is the speed of your data sources, which we will cover next.
Data source considerations
When dealing with slow queries, a good question is where your queries spend their time. Even if you
manage to keep the memory footprint of your queries small, if your data source is slow, you will wait
for your data to come in. In other words, choosing the right data source can greatly impact your query
performance.
Data sources and speed
While it’s not always feasible to swap to a different data source, it’s good to be aware of some general
rules of data sources and their performance. Here are a few pointers on what to think of:
• Relational databases: These databases tend to perform better than files. A bonus is that many
database connectors support query folding and allow you to load your data incrementally.
• Files: Store data in CSV or text files when working with files. They perform much better than
JSON, XML, and Excel files.
• Network location: Retrieving files from a network location like OneDrive or SharePoint tends to
be slower than local files. While developing, it is a good idea to consider storing your files locally.
• Web/internet: Connecting to web services or APIs may be slow. Similarly, importing large
quantities of data over the internet can be slow.
When working with the above data sources, they likely contain your source data. You may find yourself
in a situation where the nature of these sources is slowing down your queries, especially when you’re
working with legacy data sources or data from network locations. If you find yourself in that situation,
you can consider making use of a feature called dataflows, which we will cover next.
Using dataflows
Some data sources can be challenging to optimize. They may not support query folding, are naturally
slow, or lack the resources to handle large data requests. You may be unable to do much to speed them
up in these cases. However, if you’re working with data from a slow source, consider using a dataflow,
also known as an analytical dataflow.
A dataflow connects to a data source using Power Query. You can set it up to collect data from your
slow source regularly. Once it gathers the data, the dataflow stores it in Power BI’s Azure Data Lake
Storage. Here, the data is kept in a format known as CDM (Common Data Model) folders. This folder
includes CSV files for each part of your data and a JSON metadata file that describes the data’s content
and structure.
Now, remember that data source that used to be slow? You can use the dataflow to store its data. Instead
of connecting directly to the slow source, your queries can connect to the dataflow in Power Query,
which can significantly speed up your query processing.
664 Optimizing Performance
For companies with Power Premium, the enhanced compute engine is a great feature to improve
dataflows. This engine speeds up how fast dataflows respond to your requests. The best part is its
ability to use query folding. Queries sent to retrieve data from the dataflow are executed against the
cache in SQL rather than the CDM folder for dataflows without this feature turned on.
We’ve discussed how dataflows can be a great platform to store the data from your slow data sources.
Another advantage is their ability to store your queries’ output. When you have many queries in the
query editor, it tends to become slow, most likely due to the overhead of loading queries in the background.
To make the user interface faster, it’s useful to reduce the number of queries. A good way to
do this is by saving some of your queries’ logic in a dataflow. Then, you can create a single query that
connects to this dataflow to get the results of your original queries. This approach not only speeds up
the user interface but also makes your queries refresh faster as the dataflow simply returns the output
of your queries instead of calculating it during refresh time.
To learn more about dataflows, you can read this article: https://learn.microsoft.com/en-us/
power-bi/transform-model/dataflows/dataflows-create.
Performance tips
Optimizing performance is a complicated topic, even after learning about all the different perspectives
in this chapter. As with all optimization topics, you’ll have to test different approaches for your
situation. However, we have several general performance tips:
• Connect your query to a fast data source. If your source system is slow, consider moving the
data to a faster source or storing your data in a dataflow.
• Maximize the number of steps that your query folding mechanisms can fold. If you’re adept
at SQL, consider crafting an efficient SQL query and apply the EnableFolding parameter. If
SQL is not your strength, prioritize placing foldable steps early in your query. This approach
helps ensure that your data source handles most of the transformations.
• When your query steps no longer fold, the next focus should be reducing your query’s memory
usage. That means filtering rows and removing columns as soon as possible. Also reduce your
query size as much as possible before performing expensive buffering operations such as a
group by, pivoting, or joins. Subsequent steps then only need to apply their logic on a smaller
dataset.
• Use buffer functions for steps that repeatedly request data. When doing that, make sure to
buffer only the data that is required. This means that if you need to buffer the column values
only, only buffer that column; don’t buffer the entire table.
While the above are general recommendations to make your queries performant, we also have some
recommendations that improve query speed while developing in the query editor:
• Consider working with a subset of your data to speed up your development. Filter rows as early
as possible so the remaining query has fewer processing needs.
• Attempt to use streaming operations as much as possible at the start of your query. This ensures
the preview screen populates quickly and provides a better developing experience. Once
buffering operations come in, your query will need to evaluate all the rows in your dataset.
Chapter 15 665
• Once the number of transformations and queries increases, Power Query tends to get sluggish.
Consider storing (part of) your logic in a dataflow to reduce the amount of resources used
while developing.
Summary
In this chapter, we have learned the multi-faceted approach required to optimize performance in
Power Query. We have delved into areas such as the effective use of query folding and strategies for
navigating the formula firewall. The difference between buffering and streaming operations, along
with the careful use of buffer functions and the importance of reducing memory usage in your queries,
were important areas of focus.
Key to this chapter is the understanding that the careful management of memory aspects helps improve
query performance. By integrating these concepts and approaches, you are now equipped with
a comprehensive understanding of the various elements that contribute to the performance optimization
of your Power Query operations.
In the following chapter, we will focus on working with extensions. You’ll learn the fundamental concepts
required to create a custom connector and what tools are involved in the process.
Learn more on Discord
Join our community’s Discord space for discussions with the author and other readers:
https://discord.gg/vCSG5GBbyS

16
Enabling Extensions
Power Query supports a wide array of different data source connectors for accessing and retrieving
data from a variety of different data sources. In fact, Power BI Desktop natively supports almost 200
different data source connectors, most of which have been covered in Chapter 3, Accessing and Combining
Data. In addition, many of these data source connectors are generic for various data format
standards and protocols such as the Open Data Protocol (OData), Open Database Connectivity (ODBC),
JavaScript Object Notation (JSON), Extensible Markup Language (XML), and Parquet. Support for
these common standards and protocols significantly expands the potential data sources for Power
Query to thousands, if not tens of thousands of potential sources.
Even with all the data sources already supported by Power Query, there is still a need to support additional
data sources. In particular, various web services often provide an application programming
interface (API) that must be used to access their data. This chapter demonstrates how to create custom
extensions for Power Query to handle these kinds of scenarios, including the following topics:
• What are Power Query extensions?
• Preparing your environment
• Creating a custom connector
• Installing and using a custom connector
Technical requirements
You will require the following to complete the tasks in this chapter:
• Visual Studio Code
• Power Query SDK
What are Power Query extensions?
In short, Power Query extensions are primarily pieces of M code that add to the global environment.
Refer to Chapter 7, Conceptualizing M, to understand more about the global environment. Extensions
are primarily used to create custom data connectors but can also be used to create a library of reusable
custom functions.
668 Enabling Extensions
These extensions come in four different file types (file extensions):
• m: This is a simple text file containing M code.
• Pq: This is a simple text file containing M code.
• mez: This is a standard ZIP file containing custom connector files. Can use a standard ZIP file
and rename it from file.zip to file.mez.
• pqx: This is an Open Packaging Conventions (OPC) ZIP file containing custom connector files.
Typically packaged using the System.IO.Packaging .NET library, this packaging format allows
the digital signing of the file and is the format for Microsoft-certified connectors.
As noted, .mez and .pqx files are collections of files used to build custom data source connectors. We
will explore creating a custom connector later in this chapter. These file collections always include a
single .m or .pq file, which is the primary file that holds the custom connector code.
It’s important to note that Power Query extensions are implemented using the section keyword and
functions within the section are exposed to the global environment using the shared keyword. Sections
are implemented as standard M code with some special syntax rules.
Consider the following code. Note the special use of semicolons (;) within the section syntax. Just as
multiple expressions within a let statement are separated with a comma (,), multiple expressions
within a section are separated by semicolons:
section MyAwesomeExtension;
shared MyAwesomeExtension.DoSomething = (firstNumber as number, secondNumber as
number) as number =>
MyAwesomeExtension.SquareNumber(firstNumber) + MyAwesomeExtension.
SquareNumber(secondNumber);
MyAwesomeExtension.SquareNumber = (paramNumber as number) as number =>
paramNumber * paramNumber;
This section has one shared function, MyAwesomeExtension.DoSomething, and one internal
function only accessible within the section, MyAwesomeExtension.SquareNumber.
The MyAwesomeExtension.DoSomething function calls MyAwesomeExtension.SquareNumber to square
the two numbers and add them together.
Paste this code into a text editor and save the file with a file name of MyAwesomeExtension.pq. Place
this file into the [Documents]\Microsoft Power BI Desktop\Custom Connectors folder. You may
have to create this folder.
Open Power BI Desktop and navigate to File | Options and settings | Options. Under the GLOBAL
heading, choose Security, and then under Data Extensions, select the radio button for (Not Recommended)
Allow any extension to load without validation or warning:
Chapter 16 669
Figure 16.1: Enable uncertified extensions
Close Power BI Desktop and then relaunch Power BI Desktop. Open the Power Query editor in Power
BI Desktop and create the following query:
let
Source = MyAwesomeExtension.DoSomething(10, 5)
in
Source
This query returns 125, the result of squaring 10 and 5 and adding them together. Note that only
the MyAwesomeExtension.DoSomething function is exposed to and can be used in the query. The
MyAwesomeExtension.SquareNumber function cannot be referenced as it is not prefaced with the
shared keyword.
At this point, you should be able to understand the basic structure and nature of Power Query extensions
but may be asking yourself how you might use them.
What can you do with extensions?
There are two primary uses for extensions within Power Query:
• Custom function library
• Custom data source connectors
670 Enabling Extensions
As you have already seen, you can use extensions to provide a library of custom, reusable functions
for use within your queries. Instead of copying and pasting code between projects, you can instead
include your custom functions in a section with the shared keyword. While this ability exists, it is not
the primary use for extensions and is not widely used.
Custom data source connectors are the other primary use for Power Query extensions. While Power
BI Desktop comes with almost 200 connectors, there are still many other data sources, especially data
sources that use custom APIs, which are not included in the product. Power Query extensions allow
developers to create custom connectors that connect to these additional data sources. In this chapter,
we use Discord, a popular instant messaging and Voice over Internet Protocol (VoIP) platform as an
example.
Custom connectors can be used to create new data sources or customize and extend existing sources.
When installed, these connectors extend Power Query’s global environment, providing new data
access functions are not present within the M language by default.
The rest of this chapter focuses on the creation of a custom data source connector, which starts with
the processing of preparing your environment for development.
Preparing your environment
To prepare your environment, you must download and install two software components:
• Visual Studio Code
• Power Query software development kit (SDK)
In addition, the custom connector example covered in this chapter connects to Discord, a popular
voice and instant messaging social platform. We will use the Discord API to connect to a Discord server
and retrieve information. To use Discord, we will also want to install and configure two additional
components, namely:
• Internet Information Services
• Discord
Let’s look at how to get these software components installed and configured.
Getting Visual Studio Code
There are currently two versions of Power Query: a version released in 2017 that works as an extension
for Visual Studio 2017 and 2019 and a new version (currently in preview on the publication date for
this book) that works with Visual Studio Code.
For this book, we will be working with the new version of the Power BI SDK for Visual Studio Code.
To get started, download and install the latest version of Visual Studio Code here: https://code.
visualstudio.com/download.
Chapter 16 671
If you are only familiar with Visual Studio 2017, 2019, or 2022, you will likely find Visual Studio Code
somewhat unfamiliar and perhaps a bit vexing to work with. In fact, you would not be ostracized if
you feel that the acronym GUI stands for Gratuitously Unintuitive Interface when it comes to Visual
Studio Code. There are many differences and odd quirks in the Visual Studio Code product that can
cause confusion. While a full treatment of the experience within Visual Studio Code is beyond the
scope of this book, significant effort has been made to point out where and how to navigate Visual
Studio Code for the purposes of creating a custom connector. In addition, the steps within the Creating
a custom connector section have been specifically designed to minimize the number of odd errors or
messages. Therefore, follow the steps in that section closely.
Once you have Visual Studio Code installed, the next step is to install the Power Query SDK.
Getting the Power Query SDK
The Power Query SDK can be downloaded from the Visual Studio Marketplace: https://marketplace.
visualstudio.com/items?itemName=PowerQuery.vscode-powerquery-sdk. If you instead have Visual
Studio 2017 or 2019, then you can download and install the older version of the SDK here: https://
marketplace.visualstudio.com/items?itemName=Dakahn.PowerQuerySDK. The instructions found
in this book assume that you are using the new SDK for Visual Studio Code.
Clicking the Install button on the web page for the new Power Query SDK for Visual Studio Code
launches Visual Studio Code and brings up the installation page for the Power Query SDK. Simply
click the Install button here to download and install the SDK.
Once the Power Query SDK is installed, the next step is to configure a local web server.
Setting up Internet Information Services
Internet Information Services is a web server that can be configured on any Windows PC. To configure
Internet Information Services on your local computer, follow these steps:
1. Type Turn Windows features on and off into the Windows search bar and launch the Windows
Features dialog.
Pro tip: Sometimes, simply saving your work, closing the folder, and then reopening the
folder works wonders.
672 Enabling Extensions
2. Select the check box next to Internet Information Services:
Figure 16.2: Turn on Internet Information Services
3. Click the OK button to configure Internet Information Services. By default, this automatically
selects Web Management Tools and World Wide Web Services but not FTP Server. This is fine
for our purposes.
In our example custom connector, we will be using OAuth for authentication. OAuth stands for Open
Authentication and is an open standard that allows users to grant limited access to a website or service
to third-party applications. We will use the local Internet Information Services as an endpoint for a
redirect uniform resource identifier (URI) required by the OAuth protocol.
Note that it is not overly important what redirect URI is provided and the actual URI involved doesn’t
necessarily even have to actually exist in some cases, including in the case of this example. All that is
required is that some web server is available to serve as the redirect URI endpoint.
As an alternative to using a local web server as our endpoint, we could, in theory, use nearly any
OAuth redirect URI, such as https://oauth.powerbi.com/views/oauthredirect.html. However,
this can be risky if the domain name and thus web service are not within the control of the developer
as that endpoint may, at some point, no longer be valid and would thus potentially cause an error in
the authentication process.
Chapter 16 673
Note that this kind of “not valid” error is different from a page simply not existing for a web server.
For example, jazzysneakers123.com is not a valid domain name and thus will generate an error in
the authentication process if someone attempts to use jazzysneakers.com as a redirect URI. This is
different from using an invalid page for a domain name that exists and responds to requests as a web
server. In the latter case, a 404 page not found message is generated and this will not automatically
cause the authentication process to fail.
For our development purposes, we will use the local Internet Information Services and we can refer to
this web server as localhost. Obviously, this would not be sufficient for a production custom connector
since not everyone has a local web server installed and configured by default. Production scenarios
would require the developer to set up a generally available web server and corresponding redirect
URI. This kind of production scenario is beyond the scope of this book but this could be as easy as
setting up a WordPress blog site with or without a custom domain.
The next step is to install and configure Discord, so let’s get started with it!
Setting up Discord
To set up Discord for use with the Discord API, we require the following:
• Discord client
• Discord server
• Discord application (app)
• OAuth configuration
Let’s review how to get and configure each of these items.
Discord client
To download and install the Discord client, use a web browser to navigate to https://discord.com.
Click the Download for Windows button on the home page and then run the installation to install
Discord. Alternatively, you can use the Open Discord in your browser button instead.
Discord server
To create a Discord server, simply click the + icon in the left navigation of the Discord client, as shown
in the following figure:
Figure 16.3: Add a Discord server
Choose what kind of server to create and provide a name for your server. Click the Create button to
create the server. Once created, the server will appear in the left navigation pane of the Discord client.
The next step is to create a Discord application.
674 Enabling Extensions
Discord app
To use the Discord API, we need to create a Discord app. To create a Discord app, follow these steps:
1. Use a web browser and navigate to the following web page: https://discord.com/developers/
applications.
2. Click the New Application button.
3. Provide a name for the application and then click the Create button:
Figure 16.4: Create a Discord app
4. Click the Create button to create the application.
After clicking the Create button, you are transported to the application’s settings page. This is where
we can perform the next step, configuring OAuth, so let’s do that next. This application can be added
to a Discord server although for our purposes that will not be necessary.
Discord OAuth configuration
To use the API to send requests to the Discord API on behalf of a user, the user must first authorize the
app. The configuration for this is done on the application’s settings page and requires the following
steps:
1. Click the OAuth2 link in the left navigation pane of the application settings page.
2. Note the CLIENT ID under Client information. Copy and save this client ID as we will need it
later when creating our custom connector.
3. Next, click the Reset Secret button shown in the following screenshot:
Chapter 16 675
Figure 16.5: Add an OAuth2 application redirect
4. Make sure to use the Copy button to copy your client secret key and save this key. You will need
it when creating the custom connector.
5. Now click the Add Redirect button and add the redirect http://localhost/discord/redirect,
as shown in Figure 16.5.
6. Finally, click the Save Changes button to save the redirect.
The http://localhost/discord/redirect web page does not actually exist. In fact, any URI that
used localhost could be used for this step. What is important is that localhost refers to our local PC
where a local web server is running as configured in the Setting up Internet Information Services section.
We have now finished preparing the environment. Let’s now move on to creating a custom connector
or extension.
Creating a custom connector
With Visual Studio Code, the Power Query SDK installed, our local environment configured with IIS,
and Discord elements created and configured, we can now create a custom connector for Power Query.
The full code for the custom connector for this book is available in the GitHub repository found here:
https://github.com/PacktPublishing/The-Definitive-Guide-to-Power-Query-M-/tree/main/
Chapter%2016. You can either download the TDGTPQM_Discord.zip file from Chapter 14 in the repository,
extract the files, copy and paste the connector folder somewhere on your PC, and then open
the folder in Visual Studio Code ( File | Open Folder…), or you can follow along with the rest of this
section where we build the connector from scratch.
676 Enabling Extensions
In either case, you will be well served by reading the code explanations throughout the rest of this
section. The rest of this section assumes that you are creating the custom connector from scratch.
The steps to create the connector are as follows:
1. Create the extension project.
2. Configure authentication.
3. Configure navigation and content.
Let’s look at the first step: creating an extension project.
Creating an extension project
As noted earlier, we will be creating our custom connector in Visual Studio Code, Microsoft’s current
preferred approach as of this book’s publication. To create the extension project, do the following:
1. Launch Visual Studio Code if it is not running already.
2. Access the EXPLORER, expand the POWER QUERY SDK section, and then create a new extension
project by clicking the Create an extension project button:
Figure 16.6: Create an extension project
3. When prompted, enter TDGTPQM_Discord as the New project name and press the Enter key.
4. When prompted for a folder for the workspace, create a new folder named TDGTPQM_Discord,
select that folder, and press the Select workspace button.
The following file and folder structure is created:
• TDGTPQM_Discord
• .vscode
• settings.json
Chapter 16 677
• bin
• AnyCPU
• Debug
• TDGTPQM_Discord.mez
• resources.resx
• TDGTPQM_Discord.pq
• TDGTPQM_Discord.proj
• TDGTPQM_Discord.query.pq
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
• TDGTPQM_Discord.png
Let’s take a closer look at the files created:
• settings.json: A JavaScript Open Notation (JSON) file that holds workspace settings.
• TDGTPQM_Discord.mez: This file is contained in the directory bin\AnyCPU\Debug and is the actual
extension built by the Power Query SDK that you deploy and use as your custom connector.
• resources.resx: An XML file that mainly stores strings used within the extension.
• TDGTPQM_Discord.pq: This is the main code file for the custom connector. Only a single .pq
file is allowed and its format is that of an M section.
• TDGTPQM_Discord.proj: An XML file that contains project settings.
• TDGTPQM_Discord.query.pq: Mainly used as a method of creating test queries. This file simply
contains the following code:
let
result = TDGTPQM_Discord.Contents()
in
result
This file simply references a function (TDGTPQM_Discord.Contents) defined within the
TDGTPQM_Discord.pq, as we will see later. This code is sufficient for our purposes and will
remain unchanged.
• .png files: Icons used by the connector.
To write our custom connector, we will be working with the TDGTPQM_Discord.pq file, so let’s explore
this file in greater detail.
678 Enabling Extensions
TDGTPQM_Discord.pq
This file contains the main code logic for the custom connector and is the file we will be modifying
to create our custom connector. The entire custom connector is implemented as a section as per this
code at the top of the file:
// This file contains your Data Connector logic
[Version = "1.0.0"]
section TDGTPQM_Discord;
As explained previously, section documents show how extensions are implemented in M.
Below this section definition, we find the following code:
[DataSource.Kind="TDGTPQM_Discord", Publish="TDGTPQM_Discord.Publish"]
shared TDGTPQM_Discord.Contents = (optional message as text) =>
let
_message = if (message <> null) then message else "Hello World",
a = "Hello from HelloWorld: " & _message
in
a;
TDGTPQM_Discord.Contents is the main function we will use to return the contents of our connector.
Note the shared keyword preceding the function definition. This means that this function can be
referenced outside of the section.
Also, note the definition for DataSource.Kind and Publish.DataSource.Kind is defined as
TDGTPQM_Discord. Using the shared keyword along with a DataSource.Kind literal attribute definition
for the function associates the function with a specific data source, the TDGTPQM_Discord data source.
The next block of code is the definition record for the associated data source, TDGTPQM_Discord:
// Data Source Kind description
TDGTPQM_Discord = [
Authentication = [
// Key = [],
// UsernamePassword = [],
// Windows = [],
Anonymous = []
]
];
The data source definition record defines the data source’s supported authentication types. In this
case, only anonymous or implicit authentication is supported as currently defined.
Chapter 16 679
In addition to the DataSource.Kind literal attribute, there is also a Publish literal attribute associated
with the TDGTPQM_Discord.Contents function. The block of code below the data source definition
record is the Publish record definition:
// Data Source UI publishing description
TDGTPQM_Discord.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.
LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = TDGTPQM_Discord.Icons,
SourceTypeImage = TDGTPQM_Discord.Icons
];
The Publish record is used by the Power Query user interface (UI) (Power Query editor, for example) to
display the data source extension via the Get Data dialog. These settings can be adjusted to change the
behavior of certain UI elements such as the category in which the data source connector is displayed,
whether the connector is in Beta or not, and the Learn More link for the connector.
This Publish record references an additional record, TDGTPQM_Discord.Icons, which holds references
to the .png image files:
TDGTPQM_Discord.Icons = [
Icon16 = { Extension.Contents("TDGTPQM_Discord16.png"), Extension.
Contents("TDGTPQM_Discord20.png"), Extension.Contents("TDGTPQM_Discord24.png"),
Extension.Contents("TDGTPQM_Discord32.png") },
Icon32 = { Extension.Contents("TDGTPQM_Discord32.png"), Extension.
Contents("TDGTPQM_Discord40.png"), Extension.Contents("TDGTPQM_Discord48.png"),
Extension.Contents("TDGTPQM_Discord64.png") }
];
These are the icons displayed within the Power Query UI for the connector.
This completes our detailed exploration of the TDGTPQM_Discord.pq file. You might be surprised
about the small amount of code required to create a custom connector; make no mistake, the default
extension project created by the Power Query SDK is already a functioning custom connector. That
said, this connector right now does not really do anything other than essentially return the equivalent
of Hello World.
With our extension project created, we can now move on to writing our custom code for authentication.
Configuring authentication
With our base extension project created and explained, it is now time to turn our attention to customizing
the extension to meet our needs of connecting to Discord. Our first task in this regard is to code
and configure the authentication for the connector.
680 Enabling Extensions
Discord uses the OAuth security protocol for authentication. There are other authentication types
available:
• Anonymous
• AAD
• Username and password
• Windows
• Key
Each of these authentication mechanisms is unique. More information about these additional authentication
types can be found here: https://learn.microsoft.com/en-us/power-query/handlingauthentication.
OAuth authentication is by far the most complex authentication method for Power Query extensions
and thus why it was chosen for this book as an example. As mentioned previously, OAuth stands for
Open Authentication and is an open standard that allows users to grant limited access to a website or
service to third-party applications. In our case, the user is granting our third-party custom connector
limited access to their Discord information.
The OAuth protocol allows access without the user having to share their credentials (usernames and
passwords).
Instead of credentials like usernames and passwords, OAuth uses the exchange of client secrets and
access tokens between the user, the application requesting access, and the service providing the
resources.
By implementing OAuth, service providers can ensure that user credentials remain protected and
are not exposed to external applications. OAuth enables users to control and revoke access to their
resources at any time, providing them with increased security and control over their data.
Overall, OAuth plays a crucial role in enabling secure and controlled access to resources, promoting
interoperability, and enhancing user privacy in various web and mobile applications. You have very
likely seen OAuth authorization prompts in the past, though you may not have recognized what they
were.
To implement our OAuth authentication, we need to perform the following steps:
1. Add client ID and client secret files.
2. Add configuration settings.
3. Create OAuth functions.
4. Modify the data source definition.
OAuth provides a secure and standardized way for users to authorize external applications
to access their data or perform actions on their behalf.
Chapter 16 681
5. Add a credential.
6. Test the connection.
Adding client ID and client secret files
The first step in configuring the authentication for our custom connector is to add files to store our
application’s client ID and client secret. To do this, perform the following steps:
1. With the extension project open, select File | New File from the Visual Studio Code menu.
2. When prompted, enter client_id as the file name, press the Enter key, and then press the
Create File button.
3. The client_id file is automatically opened in Visual Studio Code. On line 1, simply paste in
the client ID that you saved as part of the Discord OAuth configuration section.
4. Repeat these steps but, this time, use client_secret for your file name and paste in your client
secret saved as part of the Discord OAuth configuration section.
The files you just created will be used as part of the OAuth configuration settings for our custom connector,
so let’s add those configuration settings now.
Adding Configuration Settings
Configuration settings are simply variables defined within our custom connector. These variables
can be thought of as global variables in the sense that any expression or function defined within our
custom connector can access and use these variables.
To add the configuration settings, do the following:
• At the top of your file, you will see the following line:
Section TDGTPQM_Discord;
• Paste the following code immediately under the preceding line:
// OAuth Configuration Settings
client_id = Text.FromBinary(Extension.Contents("client_id"));
client_secret = Text.FromBinary(Extension.Contents("client_secret"));
redirect_uri = "http://localhost/discord/redirect";
authorize_uri = "https://discord.com/api/oauth2/authorize?";
token_uri = "https://discord.com/api/oauth2/token";
// Static variables
api_uri = "https://discord.com/api";
// Connector window
windowWidth = 1200;
windowHeight = 1000;
682 Enabling Extensions
To better explain the variables created, consider the following:
• client_id: This reads the client_id file created in the Adding client ID and client secret files
section and stores its value.
• client_secret: This reads the client_id file created in the Adding client ID and client secret
files section and stores its value.
• redirect_uri: This stores the redirect URI used when configuring the Discord OAuth in the Discord
OAuth configuration section. This must be the same redirect URI used in that configuration.
• authorize_uri: This holds the value for Discord’s OAuth authorization URI.
• token_uri: This holds the value for Discord’s token-granting URI.
• api_uri: This stores the base Discord API URI.
• WindowWidth: This defines the window width for the authentication dialog.
• WindowHeight: This defines the window height for the authentication dialog.
With these configuration variables/settings in place, we can now move on to implementing the required
functions defined by Power Query’s OAuth authentication type, so let’s do that now.
Creating OAuth functions
Power Query’s OAuth authentication type requires the implementation of two fields, StartLogin and
FinishLogin. There are also two optional fields that can be implemented, Refresh and Logout. Each
of these fields must specify a function defined within our custom connector. We will implement the
required fields/functions but not the optional fields/functions as part of this custom connector.
Between the data source record definition, TDGTPQM_Discord, and the TDGTPQM_Discord.Publish
record definition, insert the following code:
//
// OAuth2 flow definition
//
StartLogin = (resourceUrl, state, display) =>
let
AuthorizeUrl = authorize_uri
& Uri.BuildQueryString(
[
client_id = client_id,
redirect_uri = redirect_uri,
response_type = "code",
scope = "identify email connections guilds guilds.members.
read"
]
)
in
[
Chapter 16 683
LoginUri = AuthorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = windowHeight,
WindowWidth = windowWidth,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
Parts = Uri.Parts(callbackUri)[Query]
in
TokenMethod(Parts[code]);
TokenMethod = (code) =>
let
Response = Web.Contents(
token_uri,
[
Content = Text.ToBinary(
Uri.BuildQueryString(
[
client_id = client_id,
client_secret = client_secret,
grant_type = "authorization_code",
code = code,
redirect_uri = redirect_uri
]
)
),
Headers = [#"Content-type" = "application/x-www-formurlencoded",
#"Accept" = "application/json"]
]
),
Parts = Json.Document(Response)
in
Parts;
It should be noted that the internal workings of Power Query’s OAuth authentication type are somewhat
murky. Scant explanation or documentation is provided by Microsoft outside of implementation code
samples. The code here is based on Microsoft’s sample GitHub connector, which can be found here:
https://github.com/microsoft/DataConnectors/tree/master/samples/Github.
684 Enabling Extensions
The StartLogin and FinishLogin function definitions will vary depending on the specifications of
each API. These functions implement the information and structures required by the Discord API.
Let’s start by taking a closer look at the StartLogin function.
The StartLogin function begins the OAuth authentication process by building out the authorization
URI expected by the Discord API. This value is stored by the AuthorizationUrl expression.
In effect, this expression is simply building a URI with specific query string parameters. The actual
URI that is built is the following: https://discord.com/api/oauth2/authorize?client_
id=1128020929558098050&redirect_uri=http%3A%2F%2Flocalhost%2Fdiscord%2Fredirect&respo
nse_type=code&scope=identify%20guilds%20guilds.members.read%20email%20connections.
Notice that the base authorization URI comes from our authorize_uri variable defined in the Adding
configuration settings section. The query string contains our client_id (yours will be different) along
with our redirect_uri, a response_type of code, and a scope parameter. The code response type tells
the Discord API what kind of authorization response we are looking to receive, and the scope defines
the permissions we are seeking to grant our third-party custom connector.
The StartLogin function returns a record containing the following fields:
• LoginUri: This is the AuthorizeUrl expression, which ultimately is the URI
provided earlier: https://discord.com/api/oauth2/authorize?client_
id=1128020929558098050&redirect_uri=http%3A%2F%2Flocalhost%2Fdiscord%2Fredire
ct&response_type=code&scope=identify%20guilds%20guilds.members.read%20email%20
connections
• CallbackUri: Our redirect_uri configuration variable/setting
• WindowHeight: Our WindowHeight configuration variable/setting
• WindowWidth: Our WindowWidth configuration variable/setting
• Context: null
To gain a better understanding of how this works, simply paste the URI provided earlier (AuthorizeUrl)
into a web browser. A Discord authorization prompt appears indicating that a third-party application
is seeking the following permissions:
• Access your username, avatar, and banner.
• Access your email address.
• Access your third-party connections.
• Know what servers you’re in.
• Read your member info (nickname, avatar, roles, etc.) for servers you belong to.
This list of permissions comes from the scope query string parameter of the URI. Click the Authorize
button. You are now redirected to the redirect_uri specified, http://localhost/discord/redirect.
This page does not actually exist, so your local web server returns a 404 Not Found page. However,
look closer at the URI in the browser; the URI will be something like this: http://localhost/discord/
redirect?code=WV9s0kMY3Rg6AsPvCzj6CgbgnUvwz0.
Chapter 16 685
Note the code parameter in the query string. Since we requested a response_type of code, this is the
code returned by the Discord server. We then use this code in the FinishLogin function to generate an
access token and complete our authorization process. This is done by feeding the code (Parts[code])
into the TokenMethod function.
In effect, the FinishLogin method retrieves the response from our redirect_uri and feeds the code
query string parameter value to the TokenMethod function. The TokenMethod function completes the
process of retrieving our OAuth access token.
To better understand this process, we can use Postman (postman.com) to emulate the behavior, as
shown in the following screenshot. Postman is an API platform that includes tools that are commonly
used to emulate complex API interactions such as authentication. Here in this Postman request, we
have chosen a POST operation (as opposed to GET):
Figure 16.7: Retrieving the OAuth access token via Postman
Our custom connector behaves the same because we have included a Headers element within our
Web.Contents function call within the TokenMethod function. Within Figure 16.7, we have specified a
post to the URI https://discord.com/api/oauth/token, which is the same as the token_uri we are
using in our custom connector.
686 Enabling Extensions
Within the Body of our Postman request, we have specified the body is x-www-form-urlencoded, the
same as implemented in our Headers for the Content-type within the TokenMethod function. Within
that body, we are encoding the following information:
• client_id: This is our client ID generated in the Discord OAuth configuration section.
• client_secret: The client secret generated in the Discord OAuth configuration section.
• grant_type: This is set to authorization_code as required by the Discord OAuth API.
• redirect_uri: Our redirect URI defined in the Discord OAuth configuration section, http://
localhost/discord/redirect.
• code: This is the code returned from the initial Discord API authorization call. In our custom
connector, this is the code generated and put into the query string of our redirect_uri. If you
pasted the example URI (AuthorizationUrl), it is this code: http://localhost/discord/re
direct?code=WV9s0kMY3Rg6AsPvCzj6CgbgnUvwz0.
Each of these pieces of information is also encoded into our request made within the TokenMethod
function.
In Figure 16.7, you can also see the response provided by Discord. The Discord token URI responds
with the following information in JSON format:
• token_type: The type of token being returned, in this case, Bearer
• access_token: The Bearer access token
• expires_in: When the token expires
• refresh_token: A token to refresh our token
• scope: The scope of the access token
Note in our TokenMethod function that we expect a Json.Document to be returned as a response
(Response).
While the specifics of the exact parameters passed within these requests vary between API OAuth
implementations, overall, the provided M code should work with minimal modifications for most
OAuth authentication scenarios. Luckily for us, Power Query’s OAuth authentication type handles
much of the heavy lifting from here on out.
For example, if we were to continue our example in Postman, for each subsequent API request (for
example, getting a list of the servers for the user), we would need to include the access token in a
parameter called Authorization with a value of “Bearer <access_token>". However, Power Query’s
OAuth authentication type handles the inclusion of the necessary headers automatically for us during
these subsequent API requests.
Now that we have implemented the required OAuth authentication type functions, we can modify our
data source record definition to change the authentication type from Anonymous to OAuth.
Modifying the data source record definition
With our required OAuth functions implemented, we can now modify our Data Source definition record
to change from Anonymous authentication to OAuth authentication. To do this, follow these steps:
Chapter 16 687
1. Find the Data Source definition record, TDGTPQM_Discord.
2. Replace the record definition with the following code. Notice that we now specify the OAuth
authentication type provided as a record that specifies our StartLogin and FinishLogin functions
for the corresponding OAuth authentication type fields:
// Data Source Kind description
TDGTPQM_Discord = [
TestConnection = (dataSourcePath) => {"TDGTPQM_Discord.Contents"},
Authentication = [
OAuth = [
StartLogin = StartLogin,
FinishLogin = FinishLogin
]
]
];
With our OAuth authentication type now defined, it is time to test OAuth authentication for our custom
connector. To do this, we must first add a credential.
Adding a credential
Adding a credential within Visual Studio Code emulates the behavior of the Power Query UI (such as
Power Query editor) when you sign in or authenticate in order to connect to a data source.
To add a credential, follow these steps:
1. If you have not already, choose File | Save All from the Visual Studio Code menu.
2. Expand POWER QUERY SDK in the Explorer pane.
3. Click Run TestConnection function. Note that you receive an error:
Failed to run the query due to Error: Failed to run the query due to Error:
Unable to find credential for 'TDGTPQM_Discord/TDGTPQM_Discord'
4. Click Evaluate current file. Note that you again receive an error message:
Credentials are required to connect to the TDGTPQM_Discord source. (Source
at TDGTPQM_Discord.)
5. Click Set credential.
6. When prompted with Choose the data source kind, choose TDGTPQM_Discord from the list.
7. When prompted with Choose a query/test file, choose TDGTPQM_Discord.query.pq from the list.
8. When prompted with Choose an authentication method, choose OAuth from the list.
Note that if you instead only see Anonymous listed, this is one of the many “quirks”
of Visual Studio Code. Try saving your work and executing Steps 3 and 4 again. If
that does not work, choose File | Close Folder from the menu, save your work if
prompted, and then reopen the folder using File | Open Folder….