Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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);

 

UsernameGroup Domain AGroup Domain B
User 1Group 1 AGroup 1 B
User 1Group 1 AGroup 2 B
User 1Group 1 AGroup 3 B
User 1Group 1 AGroup 4 B
User 1Group 1 AGroup 5 B
User 1Group 1 AGroup 6 B
User 1Group 2 AGroup 1 B
User 1Group 2 AGroup 2 B
User 1Group 2 AGroup 3 B
User 1Group 2 AGroup 4 B
User 1Group 2 AGroup 5 B
User 1Group 2 AGroup 6 B
User 1Group 3 AGroup 1 B
User 1Group 3 AGroup 2 B
User 1Group 3 AGroup 3 B
User 1Group 3 AGroup 4 B
User 1Group 3 AGroup 5 B
User 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 + B
User 1Group 1 A
User 1Group 2 A
User 1Group 3 A
User 1Group 1 B
User 1Group 2 B
User 1Group 3 B
User 1Group 4 B
User 1Group 5 B
User 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;

Untitled2.pngUntitled.png

 

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

 

Thank you!

1 ACCEPTED 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"

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

@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!

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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.