Forum Discussion
AD useraccountcontrol integer conversion
Here is the solution :
// 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 Resultthe reference table is like this :
flaghexadecimaldecimal
SCRIPT 0x0001 1 ACCOUNTDISABLE 0x0002 2 RESERVED 0x0004 4 HOMEDIR_REQUIRED 0x0008 8 LOCKOUT 0x0010 16 PASSWD_NOTREQD 0x0020 32 PASSWD_CANT_CHANGE 0x0040 64 ENCRYPTED_TEXT_PWD_ALLOWED 0x0080 128 TEMP_DUPLICATE_ACCOUNT 0x0100 256 NORMAL_ACCOUNT 0x0200 512 RESERVED 0x0400 1024 INTERDOMAIN_TRUST_ACCOUNT 0x0800 2048 WORKSTATION_TRUST_ACCOUNT 0x1000 4096 SERVER_TRUST_ACCOUNT 0x2000 8192 RESERVED 0x4000 16384 RESERVED 0x8000 32768 DONT_EXPIRE_PASSWORD 0x10000 65536 MNS_LOGON_ACCOUNT 0x20000 131072 SMARTCARD_REQUIRED 0x40000 262144 TRUSTED_FOR_DELEGATION 0x80000 524288 NOT_DELEGATED 0x100000 1048576 USE_DES_KEY_ONLY 0x200000 2097152 DONT_REQ_PREAUTH 0x400000 4194304 PASSWORD_EXPIRED 0x800000 8388608 TRUSTED_TO_AUTH_FOR_DELEGATION 0x1000000 16777216 RESERVED 0x2000000 33554432 PARTIAL_SECRETS_ACCOUNT 0x4000000 67108864 RESERVED 0x8000000 134217728 RESERVED 0x10000000 268435456 RESERVED 0x20000000 536870912 RESERVED 0x40000000 1073741824 RESERVED 0x80000000 2147483648 and to use it, add a custom function column :
#"Invoked Custom Function1" = Table.AddColumn(#"Removed Columns", "UAC", each fnConvertUAC([userAccountControl], UACRef, "decimal", "|"))
Hope it will help.
if someone comes with a better/quicker/less code way, don't hesitate to share :)
Hi,
I am getting the below error when I invoke the function.
Function:
= Table.AddColumn(user, "UAC", each fnConvertUAC([userAccountControl], UAC_Flags, "decimal", "|"))
Error:
Expression.Error: The column 'decimal' of the table wasn't found.
Details:
decimal
« decimal » is the name of the column containing the values. Power query is a case sentive language be careful with such mistakes.
Regards
Thank you very much for your quick response.
Now when I run the function, its creating an empty column.
User table:
displayNameuserAccountControl
User1 66082 User2 514 User3 66048 User4 512 User5 66080
Applied steps
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJU0lEyMzOwMFKK1YGIGAFFTA1N4HxjiAoTC7iICVgFQocp1AwDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [displayName = _t, userAccountControl = _t])
= Table.TransformColumnTypes(Source,{{"userAccountControl", Int64.Type}})
UAC_Flags table:
FlagHexDec
SCRIPT 0x0001 1 ACCOUNTDISABLE 0x0002 2 RESERVED 0x0004 4 HOMEDIR_REQUIRED 0x0008 8 LOCKOUT 0x0010 16 PASSWD_NOTREQD 0x0020 32 PASSWD_CANT_CHANGE 0x0040 64 ENCRYPTED_TEXT_PWD_ALLOWED 0x0080 128 TEMP_DUPLICATE_ACCOUNT 0x0100 256 NORMAL_ACCOUNT 0x0200 512 RESERVED null INTERDOMAIN_TRUST_ACCOUNT 0x0800 2048 WORKSTATION_TRUST_ACCOUNT 0x1000 4096 SERVER_TRUST_ACCOUNT 0x2000 8192 RESERVED null RESERVED null DONT_EXPIRE_PASSWORD 0x10000 65536 MNS_LOGON_ACCOUNT 0x20000 131072 SMARTCARD_REQUIRED 0x40000 262144 TRUSTED_FOR_DELEGATION 0x80000 524288 NOT_DELEGATED 0x100000 1048576 USE_DES_KEY_ONLY 0x200000 2097152 DONT_REQ_PREAUTH 0x400000 4194304 PASSWORD_EXPIRED 0x800000 8388608 TRUSTED_TO_AUTH_FOR_DELEGATION 0x1000000 16777216 RESERVED null PARTIAL_SECRETS_ACCOUNT 0x04000000 67108864 RESERVED null RESERVED null RESERVED null RESERVED null RESERVED null Applied steps:
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZPBjuMgDIZfpcp5DkBIQo8MeFrUBDLgTKeqRrzGPv4SSNqm073tIRLCP/bn3871WgXlzYjVW0X+EEJoOtDq5+1aSaXcZFGbIN97WOMsHViOewjgv0CvEZ4OPEeObgBtfPTwORl/V4h0EFnRO3Vy01qUkrlomyOjDOGso3WYXq8v2Syo2aNASYtRHaU9rGh8FrWFAKzylxFBR4RvjGN6IPvenW8sIpdkhQZhGKOext4oiRCXxouSklnJmkJnnR9kvyp2RcKypKG/bNnlb740FsFrN0hjI/op4FMKUaoQXoDOzp8CSjTuSZ7VCWlWc7IvULmef5WXFaWg+y3bbgP3+la7ZDB8j2mAMXvuvN7d6mevm6YuBIMNsXeHRPurfPa5pqQrBGGQHpX0erscfFGyllFeRpjbSQP8cD5q6OGQ7SiJxSJvGGdCLJPBVbbkzJi5fLK16QrpFCDJQjzBJTrbXx5AywT2HW3Y3YBEGUcPcsLjHTS7T/e8Jvy+ksmexS79AJntr4Voidh0hS7OOZ+6e+Auv0TXdWz5MV7u1ZjcNGkhAygPGLary2+J2o6SxMD/nei/X/78BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Flag = _t, Hex = _t, Dec = _t])
= Table.TransformColumnTypes(Source,{{"Flag", type text}, {"Hex", type text}, {"Dec", Int64.Type}})
= Table.TransformColumnTypes(#"Changed Type",{{"Dec", type number}})
Your code:
= (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
ResultInvoked fucntion:
= Table.AddColumn(user, "UAC", each fnConvertUAC([userAccountControl], UAC_Flags, "Dec", "|"))
Result:
displayNameuserAccountControlUAC
User1 66082 User2 514 User3 66048 User4 512 User5 66080 Dont know what I am doing wrong, appreciate you help. Thank you again.
If you are still having trouble, a small tweak to the reference table. Make the column names (i.e. Flag, Hex, Dec) all lower case | flag hex dec - this will fix the error!
