Forum Discussion
Trying to Get OU information
I have a spreadsheet with user and their full distinguished name, and I have an Excel query that shows me the "top level OU" for each user. for example a user can be called:
CN=Firstname Surname,OU=Level1,OU=Level2,OU=Level3,DC=MYDOMAIN,DC=COM
My Excel formula calculated how many OU's there were (as this can vary), and then worked out which was the last one, and extracted that, so from the above example the result would be Level3.
The formula was:
=SUBSTITUTE(MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,"OU=",CHAR(1),((LEN(A2)-LEN(SUBSTITUTE(A2,"OU=","")))/3)))+3,99),",DC=MYDOMAIN,DC=COM","")
I'm struggling to convert this to a reliable Power Query, the few attempts I've had that seem to get close then result in an error "We cannot convert a value of type Record to type Text." which i presume is becuase it thenks it's a list because of the commas.
Any help appreciated, and bear in mind I'm a newbie at this to please be gentle...
Thanks
Paste the code below into the advanced editor to examine one way of doing what you asked:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvazdcssKi7JS8xNVQguLQLROv6htj6pZak5hnCWEZxlrOPibOsb6eLv6+jpB2I7+/sqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Add OU" = Table.AddColumn(#"Changed Type", "OU", (r)=> Text.AfterDelimiter( List.Last( List.Select(Text.Split(r[Column1],","), each Text.StartsWith(_,"OU"))), "="), type text) in #"Add OU"Easy enough,
= Table.AddColumn(#"Changed Type", "Top", each Text.BetweenDelimiters([Usr], "OU=", ",", {0, RelativePosition.FromEnd}, 0))I guess py script would be more performant with large dataset,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvazdcssKi7JS8xNVQguLQLROv6htj6pZak5hnCWEZxlrOPibOsb6eLv6+jpB2I7+/sqxeqQZRKMZUKxmdj0xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Usr = _t]), #"Run Python script" = Python.Execute("import numpy as np#(lf)dataset['OU'] = np.vectorize(lambda ele: ele[-1])(dataset['Usr'].str.findall(r'(?<=OU=)[^,]+'))",[dataset=Source]), dataset = #"Run Python script"{[Name="dataset"]}[Value] in datasetHi,
Thanks for the solution ThxAlot and ronrsnfld offered, and i want to offer different solutions for user to refert to.
hello PatrickDpladis , you cna refer to th following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvazdcssKi7JS8xNVQguLQLROv6htj6pZak5hnCWEZxlrOPibOsb6eLv6+jpB2I7+/sqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=Text.PositionOf([Column1],"OU"), b=Text.PositionOf([Column1],"DC"), c=Text.Middle([Column1],a,b-1-a), d=List.Max(Text.Split(Text.Replace(c,"OU=",""),",")) in d) in #"Added Custom"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Paste the code below into the advanced editor to examine one way of doing what you asked:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvazdcssKi7JS8xNVQguLQLROv6htj6pZak5hnCWEZxlrOPibOsb6eLv6+jpB2I7+/sqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Add OU" = Table.AddColumn(#"Changed Type", "OU", (r)=> Text.AfterDelimiter( List.Last( List.Select(Text.Split(r[Column1],","), each Text.StartsWith(_,"OU"))), "="), type text) in #"Add OU"Easy enough,
= Table.AddColumn(#"Changed Type", "Top", each Text.BetweenDelimiters([Usr], "OU=", ",", {0, RelativePosition.FromEnd}, 0))I guess py script would be more performant with large dataset,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvazdcssKi7JS8xNVQguLQLROv6htj6pZak5hnCWEZxlrOPibOsb6eLv6+jpB2I7+/sqxeqQZRKMZUKxmdj0xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Usr = _t]), #"Run Python script" = Python.Execute("import numpy as np#(lf)dataset['OU'] = np.vectorize(lambda ele: ele[-1])(dataset['Usr'].str.findall(r'(?<=OU=)[^,]+'))",[dataset=Source]), dataset = #"Run Python script"{[Name="dataset"]}[Value] in datasetHi,
Thanks for the solution ThxAlot and ronrsnfld offered, and i want to offer different solutions for user to refert to.
hello PatrickDpladis , you cna refer to th following code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvazdcssKi7JS8xNVQguLQLROv6htj6pZak5hnCWEZxlrOPibOsb6eLv6+jpB2I7+/sqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=Text.PositionOf([Column1],"OU"), b=Text.PositionOf([Column1],"DC"), c=Text.Middle([Column1],a,b-1-a), d=List.Max(Text.Split(Text.Replace(c,"OU=",""),",")) in d) in #"Added Custom"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.