Power Query Download Distribution list details from Active Directory

Negi1984

New member
Joined
Nov 15, 2018
Messages
2
Reaction score
0
Points
0
Excel Version(s)
office 365
Hi All,
I am looking to download below mentioned details from Active Directory using Power Query.
1) Group mail ID/Distribution list email ID
2) Email ID & Name connected to all Distribution email ID
3) Location
4) Owner of Distribution ID
5) Last used date of distribution ID used.
Can anybody suggest me which tables I need to used to extract the above mentioned details in Active Directory using Power Query.
Also I found below mentioned code as well but its take to much time to extract the data. Till the time I tested it takes ~10 hrs to download ~110000 rows of data and was still running.
Looking forward for your valuable suggestion.

HTML:
let
    Source = ActiveDirectory.Domains("domain.local"),
    #"YourDomain" = Source{[Domain="domain.local"]}[Object Categories],
    group = #"YourDomain"{[Category="group"]}[Objects],
    #"Expanded group" = Table.ExpandRecordColumn(group, "group", {"mail", "managedBy", "member"}, {"group.mail", "group.managedBy", "group.member"}),
    #"Expanded mailRecipient" = Table.ExpandRecordColumn(#"Expanded group", "mailRecipient", {"dLMemSubmitPerms"}, {"mailRecipient.dLMemSubmitPerms"}),
    #"Expanded mailRecipient.dLMemSubmitPerms" = Table.ExpandListColumn(#"Expanded mailRecipient", "mailRecipient.dLMemSubmitPerms"),
    #"Expanded group.member" = Table.ExpandListColumn(#"Expanded mailRecipient.dLMemSubmitPerms", "group.member"),
    #"Filtered Rows to only Groups with Email addresses" = Table.SelectRows(#"Expanded group.member", each [group.mail] <> null),
    #"Expanded group.managedBy" = Table.ExpandRecordColumn(#"Filtered Rows to only Groups with Email addresses", "group.managedBy", {"mail"}, {"group.managedBy.mail"}),
    #"Expanded user" = Table.ExpandRecordColumn(#"Expanded group.managedBy", "group.member", {"mail"}, {"Email Address"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded user",{{"Email Address", "Member Email Address"}, {"group.managedBy.mail", "Owner Email Address"}, {"mailRecipient.dLMemSubmitPerms","Restriction"}, {"distinguishedName", "Distribution List Distinguished Name"}, {"group.mail", "Distribution List Email Address"}, {"displayName", "Distribution List"}}),
    #"Added Has Owner" = Table.AddColumn(#"Renamed Columns", "Has Owner", each if ([Owner Email Address] = null) then "No" else "Yes"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Has Owner",{"top", "msExchMailStorage", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "posixGroup", "securityPrincipal"}),
    #"Added Has Restriction" = Table.AddColumn(#"Removed Columns", "Has Restriction", each if([Restriction] = null) then "No" else "Yes")
in
    #"Added Has Restriction"
 
Back
Top