Trying to Get OU information

[PatrickDpladis]

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

[ronrsnfld · Accepted solution]

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"

[ThxAlot · Accepted solution]

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
    dataset

[Anonymous · Accepted solution]

Hi,

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.

[ThxAlot · Accepted solution]

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
    dataset

[ronrsnfld · Accepted solution]

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"