{"id":107465,"date":"2025-08-11T11:00:00","date_gmt":"2025-08-11T11:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107465"},"modified":"2025-08-06T07:18:11","modified_gmt":"2025-08-06T07:18:11","slug":"power-bi-tmdl-benefits-security-risks-best-practices","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/power-bi-tmdl-benefits-security-risks-best-practices\/","title":{"rendered":"Power BI TMDL View: Benefits, Security Risks & Best Practices"},"content":{"rendered":"\n

The Tabular Model Definition Language (TMDL)<\/a> provides a simpler way of defining Power BI Semantic Models<\/a>. Unlike the JSON-based Tabular Model Scripting Language (TMSL)<\/a>, TMDL uses a more accessible tab-based format for specifying DAX measures<\/a>, relationships, and Power Query<\/a> code.<\/p>\n\n\n\n

\"Example
Figure 1 – Example of TMDL format<\/em><\/figcaption><\/figure>\n\n\n\n

Benefits of Using TMDL View<\/h2>\n\n\n\n

In January 2025, Microsoft introduced the TMDL View<\/a>, which makes it easier for pro-code developers who use conventional programming languages with semantic models to share and reuse TMDL. With the click of the \u201cApply\u201d button (see Figure 2), you can update the model. If you\u2019ve ever had to update a Power Query function across multiple semantic models or dataflows, you know how time-consuming it can be, so this is a powerful new feature by comparison.<\/p>\n\n\n\n

\"Updating
Figure 2 – Updating your model in TDLM view with just the click of the ‘Apply’ button<\/em><\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

This feature has led to the sharing of TMDL scripts on social media and the launch of sites like https:\/\/fabsnippets.com<\/a> and the T<\/a>MDL Gallery<\/a>, where users exchange code and best practices. It highlights how active and generous the Power BI community is.\u00a0 <\/p>\n\n\n\n

However, with the introduction of TMDL View, I would caution the community about bad actors who might take advantage of that generosity. <\/p>\n\n\n\n

Security Risks of TMDL Scripts<\/h2>\n\n\n\n

If you plan to use a TMDL script from another website or individual, you should treat it as untrusted code<\/strong>. Untrusted code can lead to data leakage or trigger updates to your systems without your immediate knowledge. <\/p>\n\n\n\n

As of July 2025, I\u2019ve identified several risks associated with running a TMDL script in TMDL View that you should consider beforehand, along with mitigation techniques you can use to prevent any risk of a security breach.<\/p>\n\n\n\n

SQL Injection via Native Queries<\/h3>\n\n\n\n

If you are working with SQL-based sources, there is a chance you have disabled the \u201cRequire user approval for new native database queries\u201d setting (see Figure 3). This is a safety mechanism you may have turned off months ago, but when enabled, it prompts you with a message similar to the one shown in Figure 3.<\/p>\n\n\n\n

If you apply a TMDL script in Power BI Desktop, it could contain SQL code that you do not want to execute. You can consider this a potential form of SQL injection<\/a> within Power Query.<\/p>\n\n\n\n

\"Screenshot
Figure 3 – Screenshot of disabling user approval for native database queries<\/em><\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

This SQL injection can perform actions such as updating permissions, extracting sensitive data, or creating a user.\u00a0<\/p>\n\n\n\n

Mitigation Technique<\/h4>\n\n\n\n

Turn on \u201cRequire user approval for new native database queries\u201d<\/strong> before running the TMDL script in TMDL View. When this setting is enabled, you will see a message similar to Figure 4 before any native database queries are executed.<\/p>\n\n\n\n

\"Example
Figure 4 – Example of security prompt in Power BI desktop, prompting if you want to run a native SQL script<\/em><\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

You should also inspect the Power Query code for any database connection functions that include a Query argument, such as Sql.Database<\/a>, Oracle.Database<\/a>, Odbc.Query<\/a>, and PostgreSQL.Database<\/a>. The Query argument contains the script that will be executed against your database.<\/p>\n\n\n\n

Python and R Execution Risks<\/h3>\n\n\n\n

You may not be aware that Python<\/a> and R<\/a> also depend on the \u201cNative Database Queries\u201d<\/a><\/strong> setting. When this setting is enabled, it will prompt you to confirm whether you want to execute the code. However, I often see this setting disabled for the sake of \u2018psychological acceptability\u2019 – the principle that security warnings should match user expectations. Unfortunately, that expectation is often to avoid them entirely. If you\u2019re using Python or R in your Power BI Desktop files, you could accidentally allow code to execute that you did not intend. Python or R code can perform many actions on your machine and could even be used to extract information from your operating system that you may not want to allow (see example in Figure 5).<\/p>\n\n\n\n

\"Example
Figure 5 – Example of Python code in Power Query<\/em><\/figcaption><\/figure>\n\n\n\n

Mitigation Technique<\/h4>\n\n\n\n

Turn on \u201cRequire user approval for new native database queries\u201d<\/strong> before running the TMDL script in TMDL View and inspect the Power Query code for any use of R.Execute or Python.Execute.<\/p>\n\n\n\n

Privacy Settings and Data Leakage<\/h2>\n\n\n\n

If you see the term \u201cfastCombine\u201d<\/strong> under a model in a TMDL script (see Figure 6), you should inspect it carefully as it is shorthand for setting the model to “Always ignore Privacy Level Settings.”<\/strong> This setting disables privacy checks that normally protect data when combining sources from different locations. For example, it can be useful when combining data from a public website with internal data for analysis. However, it could also be used to pull data from an Excel file and send it to a web service without your knowledge.<\/p>\n\n\n\n

\"Example
Figure 6 – Example of fastCombine in TMDL Script<\/em><\/figcaption><\/figure>\n\n\n\n

Mitigation Technique<\/h4>\n\n\n\n

Remove fastCombine<\/strong> from the script and make sure the setting \u201cAlways combine data according to your Privacy Level settings for each source\u201d<\/strong> is enabled under Global > Security > Privacy Levels. With these settings turned on, you will be prompted when data sources are combined and can deny any combinations you find suspicious.<\/p>\n\n\n\n

\"Set
Figure 7 – Set Privacy levels to the most restrictive before running an untrusted TMDL script<\/em><\/figcaption><\/figure>\n\n\n\n

Web.Contents and External Data Transfers<\/h3>\n\n\n\n

Web.Contents<\/a> is a powerful function that can access websites and application programming interfaces, but it can also send data outside your organization. For example, Figure 8 shows a TMDL script with a Web.Contents function that could upload data to an external endpoint. When Web.Contents uses the Content argument, it becomes a POST request<\/a>, and you will be asked to connect via an anonymous connection the first time you make this connection on your machine.<\/p>\n\n\n\n

Once established, you will not be asked to set up that connection again unless you clear the permissions in ‘data source settings’. While you may be prompted to connect anonymously, be cautious of domain spoofing<\/a>, where the URL is crafted to look like a legitimate site. For instance, powerbi.com is a legitimate Microsoft site, but powerb\u00ec.com (with an accented \u2018\u00ec\u2019) is not. You could be tricked into thinking you are connecting to a trusted site when in fact, you are sending data to a malicious one.<\/p>\n\n\n\n

\"Example
Figure 8 – Example of Web.Contents performing a POST request<\/em><\/figcaption><\/figure>\n\n\n\n

Mitigation Technique<\/h4>\n\n\n\n

Make sure the setting \u201cAlways combine data according to your Privacy Level settings for each source\u201d<\/strong> is enabled under Global > Security > Privacy Levels in Power BI Desktop. Inspect the TMDL script for the presence of Web.Contents and verify that it is performing legitimate data calls. If the Content parameter is used, scrutinize its use carefully, as anonymous connections sending data can be highly suspicious.<\/p>\n\n\n\n

Folder.Files and Local File Access<\/h2>\n\n\n\n

Folder.Files<\/a> allows you to access file contents on your machine but can also be used to retrieve sensitive documents from your computer.<\/p>\n\n\n\n

\"Example
Figure 9 – Example of Folder.Files accessing a C Drive if executed<\/em><\/figcaption><\/figure>\n\n\n\n

Mitigation Technique<\/h4>\n\n\n\n

Make sure the setting \u201cAlways combine data according to your Privacy Level settings for each source\u201d<\/strong> is enabled under Global > Security > Privacy Levels in Power BI Desktop. Inspect the TMDL script for the presence of Folder.Files and ensure it is performing legitimate data calls.<\/p>\n\n\n\n

Considerations<\/h2>\n\n\n\n

If you\u2019re reading this and wondering how someone could miss those risks in a script, the answer lies in advances in obfuscation – the act of hiding a code\u2019s purpose by making it confusing or hard to read. <\/p>\n\n\n\n

Furthermore, with Large Language Models (LLM)<\/a> like ChatGPT<\/a>1<\/sup> , it has become quite simple to obfuscate code or bury it between other steps. For example, in Figure 10, I have highlighted a Web.Contents call hidden inside code that generates a Calendar\/Date dimension. In Figure 11, I simply asked an LLM model to hide the Python code that pulls files from a specific C drive. When a script is large or obfuscated, such code can easily be overlooked.<\/p>\n\n\n\n

\"Example
Figure 10 – Example of Web.Contents placed between steps that build a Calendar\/Date dimension table<\/em><\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

\"Example
Figure 11 – Example of Python code obfuscated<\/em><\/figcaption><\/figure>\n\n\n\n

Hiding malicious code<\/h3>\n\n\n\n

It\u2019s never been easier to share Power BI scripts online\u2014but it has also never been easier to hide malicious behavior within them. <\/p>\n\n\n\n

With the rise of large language models, anyone can now generate long and realistic-looking Power Query scripts that include harmful code disguised among seemingly routine steps. These scripts might load a calendar table, sort data, or calculate metrics\u2014while quietly sending data to an unknown website or accessing local files in the background. <\/p>\n\n\n\n

What\u2019s especially concerning is that these steps can be hidden in plain sight. Obfuscated code can use misleading step names, redundant transformations, or string concatenation to mask its true purpose. And with AI-generated scripts becoming more common in developer forums and social media, it\u2019s increasingly difficult to tell trusted code from tampered code at a glance. <\/p>\n\n\n\n

Stay safe.<\/strong> Don\u2019t assume a script is safe just because it looks professional or comes from a well-meaning source. Always review the steps carefully, especially those involving functions like Web.Contents, Folder.Files, or Execute blocks for Python or R.\u00a0<\/p>\n\n\n\n

If you\u2019re unsure what a complex script is doing, it can help to use a tool\u2014or even a language model\u2014to explain the code rather than write it. Asking for a plain-language summary of each step is often enough to uncover suspicious behavior. <\/p>\n\n\n\n

Conclusion<\/h2>\n\n\n\n

We have a strong community in Power BI, and TMDL View offers a huge benefit to improve your development processes. However, you should be cautious about running TMDL scripts you find online without first inspecting their contents. Doing so will help protect your data and safeguard the community from bad actors.<\/p>\n\n\n\n

Did I miss any security considerations? Let me know in the comments.<\/p>\n\n\n\n

1<\/sup>\u00a0I used\u00a0this simple\u00a0prompt\u00a0in ChatGPT: \u201cPlease obfuscate this Power Query Code denoted by ~~~ and remove comments\u201d.\u00a0<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

Explore the benefits and hidden risks of Power BI\u2019s TMDL View. Learn how to safely use TMDL scripts with expert tips on security and best practices.…<\/p>\n","protected":false},"author":344919,"featured_media":107492,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[159160,159166],"tags":[5528,101611],"coauthors":[159224],"class_list":["post-107465","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-powerbi","tag-business-intelligence","tag-power-bi"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/344919"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=107465"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107465\/revisions"}],"predecessor-version":[{"id":107541,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/107465\/revisions\/107541"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/107492"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=107465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=107465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=107465"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=107465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}