topic Re: Match 2 tables while wildcards are involved... in DAX Commands and Tips

Match 2 tables while wildcards are involved...

takui12 — Wed, 20 Nov 2024 14:18:04 GMT

Hi guys, I have two tables. One of them contains 7 columns, 2 of which need to be added to the other table based on a match with the remaining 5 columns. The complexity arises because some columns don’t have precise values but instead contain "All, Other, -1, -2" (-1 is equivalent to All for numeric columns, and -2 is equivalent to Other). Therefore, I can't simply concatenate and compare them directly.

The 5 columns are "Diritto, Cliente, Utilizzatori, Associazione, Cod_Licenziatario." For example, if there are specific values in 3 columns but the other 2 contain All or -1, then for those two columns, the match is always TRUE. Conversely, if the values are Other or -2, the match is always TRUE except for cases where those specific values have been explicitly declared under the same conditions for the other columns.

Example: if I have in my target table: 100, 100, 30, 40, 60 and in this table 100, 100, All, All, 60, means that the condition to verify is just the first 2 columns and the last one. But if I have in the 2nd table 100, 100, 30, 40, 60, then the match is perfect and should be picked from that line. But also, if I have in the 2nd table, 100, 100, 30, -2, Other (for example) and that specific combination of 5 numbers isn't present, than the match should be verify with 100, 100, 30, -2, Other ...

I know it's quite complex and so I'm hitting a wall from yesterday....thank you guys

Re: Match 2 tables while wildcards are involved...

lbendlin — Wed, 20 Nov 2024 20:14:05 GMT

Use Table.AddColumn with a custom column generator. There you can implement all kinds of complex fuzzy logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Match 2 tables while wildcards are involved...

takui12 — Thu, 21 Nov 2024 12:20:01 GMT

Hi, thank you for your reply. I've prepared an excel to import directly to pbi

Maschera Cliente di Budget = the map with those 5 columns to mix

Examples = some example of outputs

Thank you!!!

edit: i've tried this code

let
// Definire la tabella di mapping (sostituisci questa parte con la tua tabella reale)
MappingTable = #"Maschera Cliente di Budget", // Inserisci qui la definizione della tua tabella MappingTable

// Definisci la selezione dei campi da utilizzare per il matching
FieldSelection = [
Cliente = "Cliente",
Utilizzatore = "Utilizzatore",
Diritto = "Diritto",
Associazione = "Associazione",
Licenziatario = "Licenziatario"
],

// Funzione per trovare la corrispondenza in base ai criteri
MatchRow = (row as record, mappingTable as table, fieldSelection as record) =>
let
// Aggiungi debug per vedere i valori del record corrente
DebugValues = [
Cliente = Record.Field(row, fieldSelection[Cliente]),
Utilizzatore = Record.Field(row, fieldSelection[Utilizzatore]),
Diritto = Record.Field(row, fieldSelection[Diritto]),
Associazione = Record.Field(row, fieldSelection[Associazione]),
Licenziatario = Record.Field(row, fieldSelection[Licenziatario])
],

// Filtra la mappingTable in base ai criteri
FilteredTable = Table.SelectRows(mappingTable, each
(
( Record.Field(_, fieldSelection[Cliente]) = Record.Field(row, fieldSelection[Cliente]) or Record.Field(_, fieldSelection[Cliente]) = "All" or (Record.Field(_, fieldSelection[Cliente]) = "Altro" and not List.Contains(List.RemoveMatchingItems(Table.Column(mappingTable, fieldSelection[Cliente]), {"All", "Altro"}), Record.Field(row, fieldSelection[Cliente])))) and
( Record.Field(_, fieldSelection[Utilizzatore]) = Record.Field(row, fieldSelection[Utilizzatore]) or Record.Field(_, fieldSelection[Utilizzatore]) = "All" or (Record.Field(_, fieldSelection[Utilizzatore]) = "Altro" and not List.Contains(List.RemoveMatchingItems(Table.Column(mappingTable, fieldSelection[Utilizzatore]), {"All", "Altro"}), Record.Field(row, fieldSelection[Utilizzatore])))) and
( Record.Field(_, fieldSelection[Diritto]) = Record.Field(row, fieldSelection[Diritto]) or Record.Field(_, fieldSelection[Diritto]) = "All" or (Record.Field(_, fieldSelection[Diritto]) = "Altro" and not List.Contains(List.RemoveMatchingItems(Table.Column(mappingTable, fieldSelection[Diritto]), {"All", "Altro"}), Record.Field(row, fieldSelection[Diritto])))) and
( Record.Field(_, fieldSelection[Associazione]) = Record.Field(row, fieldSelection[Associazione]) or Record.Field(_, fieldSelection[Associazione]) = "All" or (Record.Field(_, fieldSelection[Associazione]) = "Altro" and not List.Contains(List.RemoveMatchingItems(Table.Column(mappingTable, fieldSelection[Associazione]), {"All", "Altro"}), Record.Field(row, fieldSelection[Associazione])))) and
( Record.Field(_, fieldSelection[Licenziatario]) = Record.Field(row, fieldSelection[Licenziatario]) or Record.Field(_, fieldSelection[Licenziatario]) = "All" or (Record.Field(_, fieldSelection[Licenziatario]) = "Altro" and not List.Contains(List.RemoveMatchingItems(Table.Column(mappingTable, fieldSelection[Licenziatario]), {"All", "Altro"}), Record.Field(row, fieldSelection[Licenziatario]))))
)
),

// Controllo del numero di righe risultanti dopo il filtro
FilteredRowCount = Table.RowCount(FilteredTable),

// Restituisce il primo record trovato, se esiste
Result = if FilteredRowCount > 0 then
Record.SelectFields(Table.First(FilteredTable), {"Code", "Utilizzatori"})
else
null
in
Result,

// Applicazione della funzione sulla tabella "Examples", passando il campo di selezione
AppliedMatchRow = Table.AddColumn(#"Union_LPF_CR_INV", "MatchResult", each MatchRow(_, MappingTable, FieldSelection)),
#"Tabella MatchResult espansa" = Table.ExpandRecordColumn(AppliedMatchRow, "MatchResult", {"Code", "Utilizzatori"}, {"MatchResult.Code", "MatchResult.Utilizzatori"})
in
#"Tabella MatchResult espansa"

it worked with a small table with few lines but when applied to the real one (200 thousands lines) it went in loop? Load of the map table...it grew from few mb to 3,5 gb then I've stopped it...something isnt working well...

Re: Match 2 tables while wildcards are involved...

lbendlin — Thu, 21 Nov 2024 13:18:26 GMT

Re: Match 2 tables while wildcards are involved...

takui12 — Thu, 21 Nov 2024 14:04:42 GMT

Sorry I've forgot the attachment, here thanks

https://drive.google.com/file/d/14uYY0wZy3BoP-j52fQM32C-g4OoVtlE9/view?usp=sharing

Data contains the data I want to match with Maschera Cliente di Budget (my map):

All the conditions (wildcards included, they are the problem for me) should be true. When it happens, Column Code and columns Utilizzatori should be bring inside table Data. Columns to match are: Cliente, Utilizzatore (be careful about last letter!), Licenziatario, Diritto, Utilizzatore

Thanks!!!

Re: Match 2 tables while wildcards are involved...

lbendlin — Thu, 21 Nov 2024 14:16:29 GMT

Is Blank the same as All ? what's the meaning of -1 and -2 ?

Not clear to me what you want to match to what.

Re: Match 2 tables while wildcards are involved...

takui12 — Thu, 21 Nov 2024 14:55:07 GMT

-1 is the same All but for numeric columns, -2 is the same as Other. Blank is a possibility, means I don't have a value for that row in that column, so it should fall inside All or Other (if blank is declared, then it fall inside Other, if isn't declared in the map table, then it fall inside All).

I want to match all the 5 conditions: Cliente, Diritto, Utilizzatore, Associazione, Licenziatario.

For example: if the map table has:

Cliente	Utilizzatore	Diritto	Associazione	Licenziatario
C0000003	140	210	All	All

It means that any record in the data table with Cliente = C000003, Utilizzatore = 140 and Diritto = 210, should be matched with row 1 in the map, and so,

Code	Utilizzatori
1	RAI Fonogrammi

These 2 values should be write in the Data table in 2 new different columns.

Also, C000003, 140, 210, 333, 444 produce the same result, because "All" condition is set on the last 2 columns of the match.

Let's see how other should work:

Code	Utilizzatori	Cliente	Utilizzatore	Diritto	Associazione	Licenziatario
40	Deejay Tv (Elemedia S.p.A.)	C0005282	190	-1	All	All
43	Varie Tv Digitali, Satellitari, Musicali	Other	190	-1	All	All

First row here specifity Customer C0005282, while the 2nd says Other. It means that, for example, if in Data we encounter something like:

C0005282, 190, 444, 555, 666 it falls under first condition and so

40	Deejay Tv (Elemedia S.p.A.)

But if we encounter something like: C000560, 190, 333, 55, 66, it should fall under

43	Varie Tv Digitali, Satellitari, Musicali

Re: Match 2 tables while wildcards are involved...

takui12 — Thu, 21 Nov 2024 15:43:47 GMT

(-1 is equivalent to All for numeric columns, and -2 is equivalent to Other). Therefore, I can't simply concatenate and compare them directly.

Code	Utilizzatori	Cliente	Utilizzatore	Diritto	Associazione	Licenziatario
40	Deejay Tv (Elemedia S.p.A.)	C0005282	190	-1	All	All
43	Varie Tv Digitali, Satellitari, Musicali	Other	190	-1	All	All

Consider these 2 lines, if in Data a line is: C0005282, 190, 444, 555, 666, it will fall under first line condition and so Code 40 should be wrote aside

If in Data I have something like: C000555, 190, 444, 555, 666 if will fall under second line condition, because Customer (Cliente) will fall under "Other", 190 is verify, and so Code 43

Re: Match 2 tables while wildcards are involved...

lbendlin — Thu, 21 Nov 2024 23:12:10 GMT

For example: if the map table has: Cliente Utilizzatore Diritto Associazione Licenziatario C0000003 140 210 All All It means that any record in the data table with Cliente = C000003, Utilizzatore = 140 and Diritto = 210, should be matched with row 1 in the map, and so, Code Utilizzatori 1 RAI Fonogrammi

There is no such customer in your sample data.

Please show examples of the expected outcome based on the sample data you provided.

Re: Match 2 tables while wildcards are involved...

takui12 — Fri, 22 Nov 2024 07:43:43 GMT

https://drive.google.com/file/d/14uYY0wZy3BoP-j52fQM32C-g4OoVtlE9/view?usp=drive_link

I've remade the file to make it more clear then... Inside:

DataWOutput: the results I wanna achieve (column Code and column Utilizzatori)

DataWithouthOutput: same as above but withouth columns, it's the table to which I wanna apply the rules

Maschera Cliente di Budget: the rules, in 5 columns, to apply to the same 5 columns in DataWithouthOutput to achieve DataWOutput

U could ignore other tabs

thanks again!

Re: Match 2 tables while wildcards are involved...

takui12 — Tue, 26 Nov 2024 06:24:14 GMT

I don't know why but i can see only till answer 9... It's possible you have quite something after it? Counter says there is an answer 10...

Re: Match 2 tables while wildcards are involved...

takui12 — Fri, 29 Nov 2024 10:11:56 GMT

sorry to pester you, may you have an idea how to solve this? thanks... 🙂

Re: Match 2 tables while wildcards are involved...

lbendlin — Fri, 29 Nov 2024 22:36:32 GMT

Sorry, I still cannot understand your mapping logic. Maybe someone else can help you further.

Re: Match 2 tables while wildcards are involved...

takui12 — Sat, 30 Nov 2024 07:46:38 GMT

I guessed so, still don't understand why your last reply is considered 11 and not 13...I've also the code in sql but the problem is to reply it in powerbi...it's complex because it's a logic with 5 variables and 2 wildcards, basically...

shortly, the idea is, for each column value, the possibilities are: specific value, All values (All or -1), other values (Other or -2).

I'll try to make it simple for whoever may read this topic:

if Columns from C to G could countains values from 1 to 100, first, is to check the fixed number in the sequence, for example 5, -1, 10, All, All, fixed numbers are are 5 and 10. -1, All and All are equivalent so it means that for every combination of these 5 columns where 5 and 10 are the only values declared in columns 1 and 3, and all is in the others, associate it to whatever I have to associate in A and B.

If the declared sequence in a line is instead All, 5, -2, 20, 40 it means:

for thesequence of 5 in the second column, 20 in the 4h and 40 in the last, with whatever in the first...the sequence is valid for whatever is not cleared declared in the 3rd column (-2 = other).

this is the trick in Other basically. Following this example if in other lines we had the same sequence but not other:

Dog, Italy, All, 5, 33, 20, 40
Cat, Spain, All, 5, 22, 20, 40
Bird, Germany, All, 5,55, 20, 40
Dolphin, France, All, 5, -2, 20, 40

this Dolphin, France, All, 5, -2, 20, 40 meant the association is valid for all it has All, 5, "NOT 22, NOT 55, NOT 33", 20, 40" And so a line like this combination:

65, 5, 18, 20, 40 will Be Dolphin, France
65, 5, 87, 20, 40 also will Be Dolphin, France

88, 5, 33, 20, 40 Will be Dog, Italy
51, 5, 33, 20, 40 also Will be Dog, Italy

88, 5, 22, 20, 40 will be Cat, Spain
All, 5, 55, 20, 40 will be Bird, Germany

I hope it's more clear