Create a many to many relationship with wildcard

[campbellmurphy]

Hi Everyone,

I have two working tables (A & B) and the ideal transformed result table (C) below in the attached Excel/PBI format. 

Ideally I would create a relationship (it would need to be many to many) between table A & B to allow the two datasets to be used in the same visualisation/have filters apply etc.  Wildcards cannot be used within the PK/FK in a relationship which means all possible permutations of the wildcards would need to be generated prior to the relationship being generated. 

I’m unsure how to generate these wildcard permutations in an efficient manner as there are 2k rows, many rows have multiple wildcard and each wildcard could be A-Z or 1-9 so 35 possible permutations per wildcard which means there are likely multiple millions of wildcard permutations when fully extrapolated.  

If there is a simpler and/or easier solution then I would greatly appreciate it. I'm open to any suggestions on how to get around the issue.

Sample Data:

Transformed Data Excel 

Transformed Data Power BI 

Thanks

[wdx223_Daniel · Accepted solution]

just delete the second condition of the FILTER function's second parameter.

like "&&len()=len()

[Smauro · Accepted solution]

Hi @campbellmurphy,

A possible PQ solution:

1) Create a new table, to go through the actual codes generated with the wildcards. This is going to be used as an intermediary to link TABLE A and TABLE B. One note, as seen with your data, in case of overlap both KPIs are linked (ABCD will get linked to AB?D and A??D)

let

    AcceptableCharacters = {"A".."Z"} & {"1".."9"},

    Codes = #"TABLE A: KPIs"[[#"Code (PK)"]],

    lenghtList = { "1"..Text.From(List.Max(List.Transform(Codes[#"Code (PK)"], Text.Length) ) ) },

    #"Added CodeList" = Table.AddColumn(Codes, "Codes", each Record.FromList(Text.ToList([#"Code (PK)"]), lenghtList ) ),

    #"Expanded Codes" = Table.ExpandRecordColumn(#"Added CodeList", "Codes", lenghtList),

    #"Fix Wildcards" = Table.TransformColumns(#"Expanded Codes", List.Transform(lenghtList, (t) => 

        {t,

        (x) => if x = "?" then AcceptableCharacters else {x} })),

    #"Fix list Items" = List.Accumulate(lenghtList, #"Fix Wildcards", (tbl, col) => Table.ExpandListColumn(tbl, col) ),

    #"Merge to text" = Table.CombineColumns(#"Fix list Items", lenghtList, (x) as text => Text.Combine(x, ""), "Code")

in

    #"Merge to text"

2) Add a column in TABLE B, to get the max Length of the code based on TABLE A:

    maxKPICodeLength = List.Max(List.Transform(#"TABLE A: KPIs"[#"Code (PK)"], Text.Length) ),

    #"Add Code" = Table.AddColumn(#"Changed Type", "Code", each Text.Start([#"Code (FK)"], maxKPICodeLength), type text)

3) Create the relationships of  #"TABLE A: KPIs"[#"Code (PK)"]<>>#"newtable"[#"Code (PK)"] and #"TABLE B: Balances"[#"Code"]<<>#"newtable"[Code"]

[campbellmurphy]

Thanks Smauro this was also a good solution.  In many ways it works even better.  I really appreciate you taking the time

[wdx223_Daniel]

try this code to generate a new table

[campbellmurphy]

Hi @wdx223_Daniel,

I really appreciate the code you’ve sent through.  When I brought it back into my actual dataset I found an additional complication which I hadn't accounted for (see below). 

The Code (FK) in Table B often has additional alphanumeric values at the end of the string relative to the values in Code (PK) so the length won’t necessarily match.  I’ve attempted to resolve this issue by using the CONTAINSSTRING function instead of LEN but haven’t had success.  Any suggestions would be greatly appreciated.

Sample Data (Updated):
Transformed Data Excel 
Transformed Data Power BI 

Thanks

[wdx223_Daniel · Accepted solution]

just delete the second condition of the FILTER function's second parameter.

like "&&len()=len()

[Greg_Deckler]

@campbellmurphy Should be able to use GENERATE