Hello everyone! I'm trying to create a report of Active Directory users from two different domains. I currently have two queries calling into both domains without any issue. Domain A is the primary domain, "users" in domain B are foreign security principles and can only be referenced by their ObjectSID (Users from Domain A inside of Domain B groups). I can create a link/relationship between the two queries/domains referencing Object SID no problem, however the results are not what I expected. The sample table below is what is happening (real data at bottom of post); UsernameGroup Domain AGroup Domain BUser 1Group 1 AGroup 1 BUser 1Group 1 AGroup 2 BUser 1Group 1 AGroup 3 BUser 1Group 1 AGroup 4 BUser 1Group 1 AGroup 5 BUser 1Group 1 AGroup 6 BUser 1Group 2 AGroup 1 BUser 1Group 2 AGroup 2 BUser 1Group 2 AGroup 3 BUser 1Group 2 AGroup 4 BUser 1Group 2 AGroup 5 BUser 1Group 2 AGroup 6 BUser 1Group 3 AGroup 1 BUser 1Group 3 AGroup 2 BUser 1Group 3 AGroup 3 BUser 1Group 3 AGroup 4 BUser 1Group 3 AGroup 5 BUser 1Group 3 AGroup 6 B Each time a new group is called from Domain A, Domain B groups will output ALL of the groups for that user, duplicating Domain A column as many times as it needs until Domain B has finished a full output of its group memberships for that user. The desired output would look something like this;UsernameGroups Domain A + BUser 1Group 1 AUser 1Group 2 AUser 1Group 3 AUser 1Group 1 BUser 1Group 2 BUser 1Group 3 BUser 1Group 4 BUser 1Group 5 BUser 1Group 6 B Obviously it would some how need to still cross reference the users objectSID. Below is a screenshot of the two tables, the relationship, and the output of duplicated data; Any ideas would be much appreciated! Go easy on me please, I'm less than a week of PowerBI/Query experience. Thank you!

Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Joining/Merge/Concat Columns

Hello everyone!

I'm trying to create a report of Active Directory users from two different domains. I currently have two queries calling into both domains without any issue. Domain A is the primary domain, "users" in domain B are foreign security principles and can only be referenced by their ObjectSID (Users from Domain A inside of Domain B groups). I can create a link/relationship between the two queries/domains referencing Object SID no problem, however the results are not what I expected. The sample table below is what is happening (real data at bottom of post);

Username	Group Domain A	Group Domain B
User 1	Group 1 A	Group 1 B
User 1	Group 1 A	Group 2 B
User 1	Group 1 A	Group 3 B
User 1	Group 1 A	Group 4 B
User 1	Group 1 A	Group 5 B
User 1	Group 1 A	Group 6 B
User 1	Group 2 A	Group 1 B
User 1	Group 2 A	Group 2 B
User 1	Group 2 A	Group 3 B
User 1	Group 2 A	Group 4 B
User 1	Group 2 A	Group 5 B
User 1	Group 2 A	Group 6 B
User 1	Group 3 A	Group 1 B
User 1	Group 3 A	Group 2 B
User 1	Group 3 A	Group 3 B
User 1	Group 3 A	Group 4 B
User 1	Group 3 A	Group 5 B
User 1	Group 3 A	Group 6 B

Each time a new group is called from Domain A, Domain B groups will output ALL of the groups for that user, duplicating Domain A column as many times as it needs until Domain B has finished a full output of its group memberships for that user.

The desired output would look something like this;

Username	Groups Domain A + B
User 1	Group 1 A
User 1	Group 2 A
User 1	Group 3 A
User 1	Group 1 B
User 1	Group 2 B
User 1	Group 3 B
User 1	Group 4 B
User 1	Group 5 B
User 1	Group 6 B

Obviously it would some how need to still cross reference the users objectSID.

Below is a screenshot of the two tables, the relationship, and the output of duplicated data;

Any ideas would be much appreciated! Go easy on me please, I'm less than a week of PowerBI/Query experience.

Thank you!

Need Help

lbendlin

3 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZxNc9pIFEX/yhTraKrmYzNLQTDGxYdGIvEilQWxVRMqDlQBNf77I48To5be617oXLLCXpzbdAuke/wqnz6NbnbH03m1/V7/uti+vhi9G/3141/zcrJebcr1YjEtLz80r4pFvlrNV7PmZT7OiuNuf745PD3Wx6wcfX5HYG/y8XxdZdXfi+zjrn6GqHlRVFlebrJfFrv9t/zhoT6daDS93MlymV1Wmx3r74d/azqgmPBExU6Uku2t5jj2dr0ZH7bHRxy8nFQ8s8gmk9lvIu7vIq4Ee18osLO5ZHNnc8nezviPQ0O95b8QGurdreTA7irJgd1VkgO7q/7QYP/UYBXUYq2gbt4rqALkfSm5YO9LyQV7z9/HiyrPihVJrMZZPuG/tBrsl/O+yOf8HjRowdNBQ31fn7e7p9P0cXdW4H98JLLl9vitfoThq/q5rB8OgkexBl48bff73f4fCVtylNU4l2C/Hp4V3A/rqQTLfwk31I/b4267Py92J8nHhEYSvGw54h3A/1DYALwuVND/QzC7VEn3D/HDm3+Xx+8Bc6cM1wl1/gsUbfwXLNX3W0Sw7XepzNNilyqAQj0/gFItvwMV7CnU8AMm1O8DJtXuQyjU7TtQwTFRvb4DZVp9B8ozoUYfMKE+HzBxINXlO1DBBQr1+AuTafEXHtjhAyjZ4AMwfrcXtPcAznb3AM029wCN9vaQLDhAqrOHUKqxB1Sqr4dQ+ktW0NVDOApEaD/Lv6awB3S6uYdLV1R4O0G0eE2pt3OAdu+BhdsD3VftlVPFv09nDUCfj6kAA006AQ8PPYB6eCWd0gUmHfMGDl2575RJMOGUUjDhmFuw6ZRkcOjKM8W0g0OH/INDF8IpI2HCKTVhwnVkTFY4dOWFTumLPhzyGH0wKTRMOmo2zATdU4zCdZgpsPQwM2D7YWawGsSOUJ42JkZsOmZITDymSmy67EtfIU/sFA0Zwb78CrcoL7+i5Un5823TziQAs0vVGJIAD4iRDo/fA+gmHKyTsh9vUFZ6vGEx13EhkoqjQ4UeUjtUAZTyGG0opi9CqGBPKVnRZlKOos3E1EQApYxECBUcE+YfQiikHUIoz6QkQ5tJuYU2EwdiJiGECi5Qyhu8MSFd8MYjLUEbisqBNhi/2ytUQBsOG4A2Gi7+bTTb9wOy4ACxdh9AsVLfpmJdPoDSX7KK5h7AUeBQWvW8Oz98/fJSTk741EMPDvb2/sLh/u4HaJaO93k/Zlivj3F1ezP8fuqvG+j7Nhzr/Tae6P8OGfIAMfrwZ84YXQgH/IALJzxBBC7cc8AbuGzAH7hswiP4cMAnRODC4yT8QgQ+3DNE4Do24B1cNuAfXLYMTPiICFx4gQN+wmYP9xQ2F/IVLpzyFm6A7GkF9hhuCOcz3AjOa7gRmN/wE4QHTfgOH054D5dO+A8frvqSh32IHyIBD7YtH8bZbLEe5y8/oLMMbTA5zxAsmJ5pMOH8kvnZBjNi4HyDw9TsB/D3BHO9xKxDD8zNO/TQyMxDn0rNPThk4O9rDlkEJmYgLDAyB2GDRXtMzENYXGImwuIicxEmmJiNsMGio0NmJGwwMCdhgzVcYl7C4hIzExZXAkVmJ2yw6AImZih6XGCOosekZiksMDZPYcElTxP0XIUVAM5WWHhwvsLCczMWJl10qMishQlG5i0sMjJzYYIVX9L07IUZgEMJ4jKfrzbZZJ5N8zIrV4v8hv8/KPwY2D94b0ZgI1JR6rcjMRWpwOHeIp1wjZ1j7ump9wIZjlgM6jtiQZT9iGaALiSdwzw5p3OuEgNZk0QM5VCSMVc5G8ivJFIg25JIodxLKgYyMcmYq1wAlKVJxjDOJhlzjRTI5yRSILuTSLlCBGV+kjFX+cBAViiWwjiiWAJojBIxpD9KRF3h6UzglhJxrGlKhLHeKRGGWqhU1lUuDcpQpWIoX5XIoexVKkZ/ExKYrVScOILhb6blPF9U2SafVSLj1YvAbVf/TUhMlx+jfBsiw+WHEXYrRlfvFvWk4b8HzGjZEbDNskM4k+XwUYsVy6Ce+WMZ8gjMXLkRnLWKRMjPArNVbgJmqtwEzlL5EZihikTID5szU5EIykpFItQJmI1yEzAT5SaI8ZyBikTIPxCYebITKOtk01Hj5EawtsmNET89SSyTG0UbJjeItktuEGyW/Bz5ZcAZJT+Cs0luBmeS/AjtTURikPwoIf6V/fk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [saa.Value.sAMAccountName = _t, saa.Value.employeeID = _t, saa.Value.title = _t, saa.Value.department = _t, domainA.group.sAMAccountName = _t, domainB.group.sAMAccountName = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"saa.Value.sAMAccountName", "saa.Value.employeeID", "saa.Value.title", "saa.Value.department", "domainA.group.sAMAccountName"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"domainA.group.sAMAccountName", "group.sAMAccountName"}}),
    #"Removed Other Columns2" = Table.SelectColumns(Source,{"saa.Value.sAMAccountName", "saa.Value.employeeID", "saa.Value.title", "saa.Value.department", "domainB.group.sAMAccountName"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns2",{{"domainB.group.sAMAccountName", "group.sAMAccountName"}}),
    Appended = #"Renamed Columns" & #"Renamed Columns2",
    #"Removed Duplicates" = Table.Distinct(Appended)
in
    #"Removed Duplicates"

3 Replies

lbendlin
Super User
3 years ago
Your sample data is unclear. Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Anonymous

Not applicable

3 years ago

lbendlin my appologies, I've attached a link to my OneDrive with the sanatized raw data.

OneDrive Source Data

The excel document contains two sheets, original data, desired data and desired data 2.

The original is what the output of the visual looks like, the desired is the concationation of domain A and Domain B into one column and desired data 2 is an alternate output if you cannot concat two columns.

I'm really just trying to get rid of the duplicates.

Thank you hope that helps!

lbendlin

Super User

3 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZxNc9pIFEX/yhTraKrmYzNLQTDGxYdGIvEilQWxVRMqDlQBNf77I48To5be617oXLLCXpzbdAuke/wqnz6NbnbH03m1/V7/uti+vhi9G/3141/zcrJebcr1YjEtLz80r4pFvlrNV7PmZT7OiuNuf745PD3Wx6wcfX5HYG/y8XxdZdXfi+zjrn6GqHlRVFlebrJfFrv9t/zhoT6daDS93MlymV1Wmx3r74d/azqgmPBExU6Uku2t5jj2dr0ZH7bHRxy8nFQ8s8gmk9lvIu7vIq4Ee18osLO5ZHNnc8nezviPQ0O95b8QGurdreTA7irJgd1VkgO7q/7QYP/UYBXUYq2gbt4rqALkfSm5YO9LyQV7z9/HiyrPihVJrMZZPuG/tBrsl/O+yOf8HjRowdNBQ31fn7e7p9P0cXdW4H98JLLl9vitfoThq/q5rB8OgkexBl48bff73f4fCVtylNU4l2C/Hp4V3A/rqQTLfwk31I/b4267Py92J8nHhEYSvGw54h3A/1DYALwuVND/QzC7VEn3D/HDm3+Xx+8Bc6cM1wl1/gsUbfwXLNX3W0Sw7XepzNNilyqAQj0/gFItvwMV7CnU8AMm1O8DJtXuQyjU7TtQwTFRvb4DZVp9B8ozoUYfMKE+HzBxINXlO1DBBQr1+AuTafEXHtjhAyjZ4AMwfrcXtPcAznb3AM029wCN9vaQLDhAqrOHUKqxB1Sqr4dQ+ktW0NVDOApEaD/Lv6awB3S6uYdLV1R4O0G0eE2pt3OAdu+BhdsD3VftlVPFv09nDUCfj6kAA006AQ8PPYB6eCWd0gUmHfMGDl2575RJMOGUUjDhmFuw6ZRkcOjKM8W0g0OH/INDF8IpI2HCKTVhwnVkTFY4dOWFTumLPhzyGH0wKTRMOmo2zATdU4zCdZgpsPQwM2D7YWawGsSOUJ42JkZsOmZITDymSmy67EtfIU/sFA0Zwb78CrcoL7+i5Un5823TziQAs0vVGJIAD4iRDo/fA+gmHKyTsh9vUFZ6vGEx13EhkoqjQ4UeUjtUAZTyGG0opi9CqGBPKVnRZlKOos3E1EQApYxECBUcE+YfQiikHUIoz6QkQ5tJuYU2EwdiJiGECi5Qyhu8MSFd8MYjLUEbisqBNhi/2ytUQBsOG4A2Gi7+bTTb9wOy4ACxdh9AsVLfpmJdPoDSX7KK5h7AUeBQWvW8Oz98/fJSTk741EMPDvb2/sLh/u4HaJaO93k/Zlivj3F1ezP8fuqvG+j7Nhzr/Tae6P8OGfIAMfrwZ84YXQgH/IALJzxBBC7cc8AbuGzAH7hswiP4cMAnRODC4yT8QgQ+3DNE4Do24B1cNuAfXLYMTPiICFx4gQN+wmYP9xQ2F/IVLpzyFm6A7GkF9hhuCOcz3AjOa7gRmN/wE4QHTfgOH054D5dO+A8frvqSh32IHyIBD7YtH8bZbLEe5y8/oLMMbTA5zxAsmJ5pMOH8kvnZBjNi4HyDw9TsB/D3BHO9xKxDD8zNO/TQyMxDn0rNPThk4O9rDlkEJmYgLDAyB2GDRXtMzENYXGImwuIicxEmmJiNsMGio0NmJGwwMCdhgzVcYl7C4hIzExZXAkVmJ2yw6AImZih6XGCOosekZiksMDZPYcElTxP0XIUVAM5WWHhwvsLCczMWJl10qMishQlG5i0sMjJzYYIVX9L07IUZgEMJ4jKfrzbZZJ5N8zIrV4v8hv8/KPwY2D94b0ZgI1JR6rcjMRWpwOHeIp1wjZ1j7ump9wIZjlgM6jtiQZT9iGaALiSdwzw5p3OuEgNZk0QM5VCSMVc5G8ivJFIg25JIodxLKgYyMcmYq1wAlKVJxjDOJhlzjRTI5yRSILuTSLlCBGV+kjFX+cBAViiWwjiiWAJojBIxpD9KRF3h6UzglhJxrGlKhLHeKRGGWqhU1lUuDcpQpWIoX5XIoexVKkZ/ExKYrVScOILhb6blPF9U2SafVSLj1YvAbVf/TUhMlx+jfBsiw+WHEXYrRlfvFvWk4b8HzGjZEbDNskM4k+XwUYsVy6Ce+WMZ8gjMXLkRnLWKRMjPArNVbgJmqtwEzlL5EZihikTID5szU5EIykpFItQJmI1yEzAT5SaI8ZyBikTIPxCYebITKOtk01Hj5EawtsmNET89SSyTG0UbJjeItktuEGyW/Bz5ZcAZJT+Cs0luBmeS/AjtTURikPwoIf6V/fk/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [saa.Value.sAMAccountName = _t, saa.Value.employeeID = _t, saa.Value.title = _t, saa.Value.department = _t, domainA.group.sAMAccountName = _t, domainB.group.sAMAccountName = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"saa.Value.sAMAccountName", "saa.Value.employeeID", "saa.Value.title", "saa.Value.department", "domainA.group.sAMAccountName"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"domainA.group.sAMAccountName", "group.sAMAccountName"}}),
    #"Removed Other Columns2" = Table.SelectColumns(Source,{"saa.Value.sAMAccountName", "saa.Value.employeeID", "saa.Value.title", "saa.Value.department", "domainB.group.sAMAccountName"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns2",{{"domainB.group.sAMAccountName", "group.sAMAccountName"}}),
    Appended = #"Renamed Columns" & #"Renamed Columns2",
    #"Removed Duplicates" = Table.Distinct(Appended)
in
    #"Removed Duplicates"

Forum Discussion

Joining/Merge/Concat Columns

3 Replies

Related content

New Column

Grouping rows by Column and then updating Column

Unpivot Column

Transform columns / type

Adding columns