Value.Firewall

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Value.Firewall

Understanding Value.Firewall

Before we dive into the M code behind Value.Firewall, let’s first understand what this function does. Value.Firewall is used to protect data sources in Power Query. It allows you to specify which data sources are trusted and which ones are not. When a user attempts to access a data source that is not trusted, Power Query will block the request and display an error message.

The purpose of Value.Firewall is to prevent unauthorized access to your data. By default, Power Query allows all data sources to be accessed. This can be a security risk, especially when dealing with sensitive data. Value.Firewall allows you to control which data sources can be accessed and who can access them.

The M code behind Value.Firewall

Now that we understand what Value.Firewall does, let’s take a look at the M code behind this function. The Value.Firewall function is actually a combination of two M functions: Value.Native and Security.Firewall.

Value.Native is used to evaluate a value within a specified context. This function is used to evaluate the results of a query step. Security.Firewall, on the other hand, is used to check if a data source is trusted or not.

The Value.Firewall function combines these two functions to evaluate a query step and check if the data source is trusted before returning the result. If the data source is not trusted, an error is returned instead of the actual data.

Here is an example of the M code behind the Value.Firewall function:

let

Source = Excel.Workbook(File.Contents("C:Data.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Age", Int64.Type}})

in

#"Changed Type"

This code reads an Excel file, selects the Sheet1 sheet, promotes the header information, and changes the data types. However, this code does not include any security measures. To add security, we can modify the code as follows:

let

Source = Excel.Workbook(File.Contents("C:Data.xlsx"), null, true),

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),

#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Age", Int64.Type}}),

#"Value.Firewall" = Value.Firewall(#"Changed Type")

in

#"Value.Firewall"

In this modified code, we have added the Value.Firewall function to secure the data. Now, only trusted data sources will be allowed to be accessed.

The Value.Firewall function is an essential function when working with sensitive data in Power Query. By controlling which data sources can be accessed, you can ensure that only authorized users can access and modify your data. In this article, we have explored the M code behind the Value.Firewall function and how it can be used to secure your data. Use this function in your Power Query workflows to protect your data and ensure that your organization’s data is secure.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)