Active Directory useraccountcontrol bit mask decode function

[phillip_from_oz]

Everyone,

I’m working through AD useraccountcontrol integer conversion post:

https://community.powerbi.com/t5/Desktop/AD-useraccountcontrol-integer-conversion/td-p/437105

There is this function (Power BI M language) in this message and it appears below:

// input is the cell with the decimal value ei:512
// tableref is the table referencing the decimal values with the 'human readable' ones
// columnref is the column name of tableref where decimal values are
// delimiter is the character to split by ei:|
// the result is a text ei:PASSWD_NOTREQD|NORMAL_ACCOUNT|DONT_EXPIRE_PASSWORD
// the function can be updated to return a table/list to have it expanded for example
(input as number, tableref as table, columnref as text,delimiter as text) as text =>
let
	//create a list with all potential values. should be 32 for this purpose
	values = {1..Table.RowCount(tableref)},
	//function to have the power of a decimal value
	fnPower = (value as number) => Number.Power(2,value),
	//function to have the bitwise "and" value. this function compare the power value against the input ei: 8/512
	fnBitwise = (value as number, v as number) => Number.BitwiseAnd(value,v),
	//all values are calculated (pow2) and the bitwise and is retrieved
	//an index column is then added to get the position of the values 
	//the result is filtered to get records that are not 0 (0 means the bitwise comparison failed)
	TableResult = Table.SelectRows(
                        Table.AddIndexColumn(
			    Table.FromList(
			        List.Transform(
				    values,
				    each fnBitwise(fnPower(_),input)
				),
				Splitter.SplitByNothing(),
				null,
				null,
				ExtraValues.Error
			    ),
			    "Index",
			    1,
			    1
                        ),
			each ([Column1] <> 0)
		    ),
	//the result is merged with the reference table on the referenced column. the inner join is used to filtered out not required values
	//then the rows are merged into one cell with the positioned delimiter
	Result = Text.Combine(
                    Table.ExpandTableColumn(
                        Table.RemoveColumns(
                            Table.NestedJoin(TableResult,{"Column1"},tableref,{columnref},columnref,JoinKind.Inner),
			    {"Column1", "Index"}
			),
			columnref
			,{"flag"}
			,{"flag"}
		    )[flag],
		    delimiter
                )
in
	Result

This does not work if the input is blank or non numeric.

For some of user rows, my AD returns blank in the User Account Control.

Questions

1. Does anyone know how to exit the function if the input is blank?

I’ve searched the internet but cannot find how to exit a function.

1. Should I check the input as blank via empty string “” or Null?

When I look at the table for this field using filter it says it is blank.

1. I should also check if the input is not numeric. How do I do this?

Thanks for any help

[v-jingzhang · Accepted solution]

Hi @phillip_from_oz 

In the custom function, input is expected to be of number type. If you don't want to keep non-numeric values in userAccountControl column, you can change this column into Decimal Number type. Blank values will become null and non-numeric values will become Error. Replace Errors with null.  

Then when you add the custom function column, check whether the input value is null. When it's not null, invoke the custom function.  

= Table.AddColumn(#"Replaced Errors", "UAC", each if [userAccountControl] = null then null else fnConvertUAC([userAccountControl], UACRef, "decimal", "|"))

If you want to keep non-numeric values in userAccountControl column, you need to keep this column of Text type. Then extract decimal number to invoke custom function when you add the column. 

= Table.AddColumn(#"Changed Type", "UAC", each let __number = Number.From([userAccountControl]) in if (try Value.Is(__number, type number) otherwise false) then fnConvertUAC(__number, UACRef, "decimal", "|") else null)

Hope it helps!

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[phillip_from_oz]

Thanks for the reply.
user.userAccountControl is already numeric when I setup the connection from my Active Directory

I did 
Get data->Blank Query

Pick the user table

Go to Applied Steps section on the right

Pick last instruction.
Right Click

Pick “Insert Step After”

Add this code

Table.AddColumn(#"Expanded user", "UAC_flag", each if [user.userAccountControl] = null then null else fnConvertUAC([user.userAccountControl], flaghexadecimaldecimal, "Dec", "|"))

Pick Close and Apply

UAC_Flag is still empty even when user.userAccountControl is not

Why is that? How do I fix this?

[phillip_from_oz]

Hi,
I've found out why the column is blank in that there is a bug in the fnConvertUAC.
I ran the code as given against the number 512 (fuction invoke) and it returned a blank.

So I recoded the entire function so there is now no table lookup. All bit operations are done by explicit code. It runs much faster as well. 

(n as number) as text =>
let
  result2 = Number.BitwiseAnd(n,1),
  val2=if result2 > 0 then "SCRIPT" else "",
  result3 = Number.BitwiseAnd(n,2),
  val3=if result3 > 0 then "ACCOUNTDISABLE" else "",
  result5 = Number.BitwiseAnd(n,8),
  val5=if result5 > 0 then "HOMEDIR_REQUIRED" else "",
  result6 = Number.BitwiseAnd(n,16),
  val6=if result6 > 0 then "LOCKOUT" else "",
  result7 = Number.BitwiseAnd(n,32),
  val7=if result7 > 0 then "PASSWD_NOTREQD" else "",
  result8 = Number.BitwiseAnd(n,64),
  val8=if result8 > 0 then "PASSWD_CANT_CHANGE" else "",
  result9 = Number.BitwiseAnd(n,128),
  val9=if result9 > 0 then "ENCRYPTED_TEXT_PWD_ALLOWED" else "",
  result10 = Number.BitwiseAnd(n,256),
  val10=if result10 > 0 then "TEMP_DUPLICATE_ACCOUNT" else "",
  result11 = Number.BitwiseAnd(n,512),
  val11=if result11 > 0 then "NORMAL_ACCOUNT" else "",
  result13 = Number.BitwiseAnd(n,2048),
  val13=if result13 > 0 then "INTERDOMAIN_TRUST_ACCOUNT" else "",
  result14 = Number.BitwiseAnd(n,4096),
  val14=if result14 > 0 then "WORKSTATION_TRUST_ACCOUNT" else "",
  result15 = Number.BitwiseAnd(n,8192),
  val15=if result15 > 0 then "SERVER_TRUST_ACCOUNT" else "",
  result18 = Number.BitwiseAnd(n,65536),
  val18=if result18 > 0 then "DONT_EXPIRE_PASSWORD" else "",
  result19 = Number.BitwiseAnd(n,131072),
  val19=if result19 > 0 then "MNS_LOGON_ACCOUNT" else "",
  result20 = Number.BitwiseAnd(n,262144),
  val20=if result20 > 0 then "SMARTCARD_REQUIRED" else "",
  result21 = Number.BitwiseAnd(n,524288),
  val21=if result21 > 0 then "TRUSTED_FOR_DELEGATION" else "",
  result22 = Number.BitwiseAnd(n,1048576),
  val22=if result22 > 0 then "NOT_DELEGATED" else "",
  result23 = Number.BitwiseAnd(n,2097152),
  val23=if result23 > 0 then "USE_DES_KEY_ONLY" else "",
  result24 = Number.BitwiseAnd(n,4194304),
  val24=if result24 > 0 then "DONT_REQ_PREAUTH" else "",
  result25 = Number.BitwiseAnd(n,8388608),
  val25=if result25 > 0 then "PASSWORD_EXPIRED" else "",
  result26 = Number.BitwiseAnd(n,16777216),
  val26=if result26 > 0 then "TRUSTED_TO_AUTH_FOR_DELEGATION" else "",
  result28 = Number.BitwiseAnd(n,67108864),
  val28=if result28 > 0 then "PARTIAL_SECRETS_ACCOUNT" else "",
  mylist={val2,val3,val5,val6,val7,val8,val9,val10,val11,val13,val14,val15,val18,val19,val20,val21,val22,val23,val24,val25,val26,val28},
  mylist_filtered = List.Select(mylist, each _ <> ""),
  myflag=Text.Combine(mylist_filtered,"|")
in
    myflag

ps. the code was generated from a powershell program so I coded just one mask and the rest were generated by powershell
All good now!

[v-jingzhang]

@phillip_from_oz 

Wow it's great! You can mark an appropriate reply as Solution to let other people find it quickly!

Best regards,

Jing