Power BI : Mask data based on a custom function

Question

I want my "Confidential" columns to be masked in Power BI. I am looking for any Custom function that can help me so that I can use it to call at the end of my transformation steps and it just masks for the column it is called on.

(I read few articles over Google but nothing worked as explained).

Something like below where the Masked column should be "masked":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvUChOTS5KLVGK1YlW8i8tgnIV0vKLcktzEq0UXG19neOMwNKeJak5OanJJaWJOQoBRfkFqUUllUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Confidential Column" = _t]),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Masked column", each FnMaskText([Confidential Column], "TestTable"))
in
    #"Invoked Custom Function"

Would expect output to be consistent(so that I can still apply aggregations):

Answer 1

There are are two approaches you could take here. You could use a hash function such as the one implemented by Alexis here:

https://community.fabric.microsoft.com/t5/Power-Query/SHA256-in-Power-Query-and-DAX-Performance-Analysis-Tools/td-p/2231130

Alternatively, given your source data like this:

Create a second table (no need to load it) that simply duplicates your first query and selects only the column in question and the distinct values of that column along with an added index.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvUChOTS5KLVGK1YlW8i8tgnIV0vKLcktzEq0UXG19neOMwNKeJak5OanJJaWJOQoBRfkFqUUllUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Confidential Column" = _t]),
    Custom1 = Source&Source&Source,
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Confidential Column"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Then simply left outer join merge table 1 to table 2 and expand the index column as your mask. You could use anything in your index as long as it is unique per row.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvUChOTS5KLVGK1YlW8i8tgnIV0vKLcktzEq0UXG19neOMwNKeJak5OanJJaWJOQoBRfkFqUUllUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Confidential Column" = _t]),
    Custom1 = Source&Source&Source,
    #"Merged Queries" = Table.NestedJoin(Custom1, {"Confidential Column"}, #"Query1 (2)", {"Confidential Column"}, "Query1 (2)", JoinKind.LeftOuter),
    #"Expanded Query1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Query1 (2)", {"Index"}, {"Masked"})
in
    #"Expanded Query1 (2)"