Removing column after calculation in Power Query (for data anonymization purpose)

[IvanS]

Hello,

I am creating data model with accounting data which means that there is information which needs to be anonymized. I have 2 tables:

Table "Invoices"

Vendor Name	Vendor ID	Invoice Nr.	Invoice Amount	Currency
AAA	111	123123	1000	EUR
BBB	222	321321	2000	EUR
CCC	333	456456	3000	USD
AAA	111	654654	500	USD
CCC	333	789789	800	GBP

Then I have another table: "Internal vendors"

Vendor ID

AAA

BBB

DDD

EEE

I need to create calculated column which will add the "Alternative Vendor Name" by looking up the "Internal Vendors" table and if the vendor is there, it will add text "INTERNAL" and if not, then it will take the name from "Vendor Name".

Now, the problem is that I need to remove the "Vendor Name" column from table "Invoices" so the end-users cannot see the original names. Hiding column is not very helpful, as users can use measures (such as SELECTEDVALUE) to get the data. As soon as I remove the column, my caluculation in "Alternative Vendor Name" is failing.

The desired result is following:

Alternative Vendor Name	Vendor ID	Invoice Nr.	Invoice Amount	Currency
INTERNAL	111	123123	1000	EUR
INTERNAL	222	321321	2000	EUR
CCC	333	456456	3000	USD
INTERNAL	111	654654	500	USD
CCC	333	789789	800	GBP

Is there any solution for that (e.g. Power BI will perform calculation first and then remove the column)?

Thank you for any suggestions!

Ivan

[v-yiruan-msft · Accepted solution]

Hi @IvanS ,

I created a sample pbix file(see attachment) for you base on provided table data, please check whether that is what you want.

1. Merge these two tables base on the column [Vendor Name] of table "Invoices" and the column [Vendor ID] of table "Internal vendors"

2. Add custom column to update the value base on the conditions

3. Remove the orignial column [Vendor Name] in the table Invoices

The full applied codes as follow:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY07DoAgEETvsjUFLB+xBDS2RkNFuP81nMUGkpdlJryF1iilRIqMMTLZAglaaxxnfairRjlnNGbGtGyA1NUppcitlXXnA5D6O/U9hjP/FbwDCH5R5me2uAOEOJQr39T7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Vendor Name" = _t, #"Vendor ID" = _t, #"Invoice Nr." = _t, #"Invoice Amount" = _t, Currency = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor Name", type text}, {"Vendor ID", Int64.Type}, {"Invoice Nr.", Int64.Type}, {"Invoice Amount", Int64.Type}, {"Currency", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Vendor Name"}, #"Internal vendors", {"Vendor ID"}, "Internal vendors", JoinKind.LeftOuter),
    #"Expanded Internal vendors" = Table.ExpandTableColumn(#"Merged Queries", "Internal vendors", {"Vendor ID"}, {"Vendor ID.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Internal vendors", "Alternative Vendor Name", each if [Vendor ID.1]=null then [Vendor Name] else "INTERNAL" ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Vendor ID.1", "Vendor Name"})
in
    #"Removed Columns"

Best Regards

[johnt75]

Create the calculated column in Power Query, then you will be able to safely delete the original column and it won't be exposed in your report.

Merge your data table with the Internal Vendors table, then create a new custom column. If the value from the merged column is null then use the original vendor name, otherwise use "INTERNAL". You can then delete the original column and any other columns introduced during the merge.

[Samarth_18]

Hi @IvanS ,

Please refer to below article. I have used it earlier in my case. Hope it help you too.

https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/ 

Thanks,

Samarth