Join 2 tables on dynamic length of Primary Key

[s_cart]

Hi Everyone,

I'm trying to compare a list of CMDB Server names to a list of active servers and their names. Hopes are to see where there are multiple servers active for a single entry in our CMDB. Hopefully the below screenshots explain what I'm trying to do:

(Screenshot in Excel for demo purposes only)

I've accomplished this via SQL, but I'd like to do this (if possible) in PowerBI rather than replicating the database elsewhere and running the changes/joins on the database side. I used a Left and the length caculated to shorted the ServerName, making sure they'd match (as our naming convention is to add anything after e.g. the -old)

Select CMDB.*, ServerList.*
FROM (CMDB LEFT JOIN [ServerList] ON [CMDB].Name =
Left([ServerList].ServerName,[CMDB].CINameLength)) 

I've tried similar in PowerQuery (M Language), with a variety of weird functions and merges, but not managed to get the results I'm expecting. Below are some of the faulty examples I worked with but couldn't get working. The default one-to-many relationship on CMDB.Name and Serverlist.Servername didn't work with an outerjoin which I'd hoped would work fine.

Tried on a merge but didn't have any success, got current results pictured above, code below:

let
    Source = Table.NestedJoin(#"CMDB",{"Name"},ServerList,Text.Start({"ServerName"},),"ServerList",JoinKind.FullOuter),
    #"Expanded ServerList" = Table.ExpandTableColumn(Source, "ServerList", {"ServerName"}, {"ServerName"}),
    #"Sorted Rows" = Table.Sort(#"Expanded ServerList",{{"Name", Order.Ascending}})
in
    #"Sorted Rows"
    

Tried in a function as well:

From the table:
--------------------

let
    Source = #"CMDB",
    #"Invoked Custom Function" = Table.AddColumn(Source, "Query1", each Query1([Name])),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query1", {"Matched Name"}, {"Matched Name"})
in
    #"Expanded Query1"
    
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  	

From the Function:
--------------------

let fnLookup = (input) =>

let
    Source = #"CMDB",
        Record = Table.AddColumn(Source, "Matched Name", each if Text.Contains(input, [Name]) then [Name] else null)
    
in
    Record
in
    fnLookup

But this returned only the first entry on the list, not the full list like we'd expected.

I'm finally stuck and given up after doing some external training and consulting our internal guru on the best way to handle this.

TL,DR: Does anyone know of a way to do a join on 2 tables, where you use the dynamic length of one column to match on the 2nd?

[Anonymous]

Hi Everyone,

(Since accounts now require AAD, had to make a new account) 

The solution a collegue just came up with, in Power Query we used the following:

let
    Source = CMDB,
    LookupVM = Table.AddColumn(Source, "LookupVM", (outer) => Table.SelectRows(vm_sample, each Text.StartsWith(Text.Upper([VM_NAME]), Text.Upper(outer[NAME])))),
    #"Expanded LookupVM" = Table.ExpandTableColumn(LookupVM, "LookupVM", {"VM_NAME"}, {"LookupVM.VM_NAME"}),
    LookupHW = Table.AddColumn(#"Expanded LookupVM", "LookupHW ", (outer) => Table.SelectRows(HW_sample, each Text.StartsWith(Text.Upper([HName]), Text.Upper(outer[NAME])))),
    #"Expanded LookupHW" = Table.ExpandTableColumn(LookupHW, "LookupHW", {"HName", "Description", "Domain", "Name-short", "PrimaryOwnerContact", "SystemLocation"}, {"LookupHW.HName", "LookupHW.Description", "LookupHW.Domain", "LookupHW.Name-short", "LookupHW.PrimaryOwnerContact", "LookupHW.SystemLocation"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded LookupHW",{"NAME"},config_sample,{"ServerName"},"config_sample",JoinKind.LeftOuter),
    #"Expanded config_sample" = Table.ExpandTableColumn(#"Merged Queries", "config_sample", {"ServerName", "Fullname", "OS", "ProcessorCount", "Warning"}, {"config_sample.ServerName", "config_sample.Fullname", "config_sample.OS", "config_sample.ProcessorCount", "config_sample.Warning"}),
    #"Sorted Rows" = Table.Sort(#"Expanded config_sample",{{"NAME", Order.Ascending}})
in
    #"Sorted Rows"

Because some have "." instead of "-" as a seperator, the marked solution does not work. This above one does.

TL,DR Use the following:

NameOfNewTable = Table.AddColumn(Source, "NameOfNewTable", (TEMP-NAME-OF-YOUR-SOURCE-DATA) => Table.SelectRows(TABLE_NAME_OF_COMPARISON, each Text.StartsWith(Text.Upper([COLUMN_NAME_OF_COMPARISON]), Text.Upper(TEMP-NAME-OF-YOUR-SOURCE-DATA[COLUMN_NAME_OF_YOUR_SOURCE_DATA]))))

[v-yuta-msft · Accepted solution]

Hi s_cart,

To achieve your requirement, please refer to steps below:

1.Duplicate Server List[ServerName].

 

2.Split [ServerName-Copy] by delimiter "-"

 

3.Remove column [Status] and [ServerName-Copy.2]

 

4.Merge CMDB and Server List like below:

  

5.Remove useless columns

 

PBIX here: https://www.dropbox.com/s/lzwqigromvyton0/Join%202%20tables%20on%20dynamic%20length%20of%20Primary%2...

Regards,

Jimmy Tao