Fuzzy Logic in PQ

anupambit1797 

Just in case here Creating a Crossjoin (All Combinations) in Power Query (gorilla.bi) are two cross join methods - one is faster, another one is easier.

Sample has nothing common with fuzzy logic. However, when needed we may imitate it for cartesian products, one of samples is in the comment to this post Cartesian Product Joins (for the Excel person) - Excelguru

anupambit1797 

The Cartesian Product

Trying to return data from two or more tables without any joins creates what is called a "Cartesian product." A Cartesian product is defined as all possible combinations of rows in all tables. Be sure you have joins before trying to return data, because a Cartesian product on tables with many records and/or on many tables could take several hours to complete. The following is a Cartesian product as used on the two example tables; note that this table is only 3 records times 3 records, which yields a total of 9 records. However, imagine if instead, the table was 100 records times 1,000 records times 10,000 records; then the table would contain 1,000,000,000 records!


Join_Field Join_Field Color_Field Pattern_Field
1 2 Red Striped
1 3 Red Checkered
1 4 Red Polka-Dot
2 2 Blue Striped
2 3 Blue Checkered
2 4 Blue Polka-Dot
3 2 Green Striped
3 3 Green Checkered
3 4 Green Polka-Dot


Occasionally, some users want to use a Cartesian product; however, most users who get them do so accidentally, and are often confused by them. Because most users exclude most of the fields in a join, a real Cartesian product can easily look as perplexing as this:


Color_Field
Red
Red
Red
Blue
Blue
Blue
Green
Green
Green


If 100 records are added to Pattern_Table, this query would have 309 records (103 records each of Red, Blue, and Green).

Cartesian products have relatively simple SELECT statements. The following is how the SQL might look for the above Cartesian product:


SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field
FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table

https://support.microsoft.com/en-us/topic/description-of-the-usage-of-joins-in-microsoft-query-59040b90-edef-1b17-6183-dbbbb2da52b9#:~:text=1%20Create%20an%20outer%20join%2C%20then%20click%20New,the%20word%20UNION%2C%20and%20close%20...%20See%20More.

https://support.microsoft.com/en-us/office/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e#:~:text=1%20On%20the%20Data%20tab%2C%20in%20the%20Get,Query%20from%20Excel.%203%20Double-click%20...%20See%20More

anupambit1797 

https://learn.microsoft.com/en-us/power-query/cross-join