Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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"
@lbendlin my appologies, I've attached a link to my OneDrive with the sanatized raw 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!
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"
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-...
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |