Forum Discussion

Anonymous's avatar
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 pri...
Need Help
  • lbendlin's avatar
    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"
Anonymous's avatar
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's avatar
    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"