Forum Discussion
Compare two Columns with placehoder ? or *
Hi all,
I will compare two Columns. The ID Column can have placeholders (like ? or *)
If there is some ? there must be one digit. If there is some * there can be zero, one or more digits.
Attachted an example. How can I compare these two columns and find some match? Is there any solution with power query?
Thanks for helping!Example
Anonymous
let your_table = Table.FromRecords( {[ID = "1NA*W*", Search = "1NA2345W78"], [ID = "1NA????W*", Search = "1NA1234W78"], [ID = "1NA????W*", Search = "1NA12W78"]} ), wildcards = [#"?" = "0123456789", #"*" = "0123456789"], comparer = (x as list, y as list) => [x_first = List.First(x), a = Record.FieldOrDefault(wildcards, x_first, x_first), star = x_first = "*", b = List.First(y), par = Text.Contains(a, b), skip_y = par, skip_x = not List.AllTrue({star, par}), next = if List.IsEmpty(y) then "Yes" else if List.IsEmpty(x) or List.AllTrue({not par, not star}) then "No" else @comparer( List.Skip(x, Number.From(skip_x)), List.Skip(y, Number.From(skip_y)) )][next], mapping = Table.AddColumn(your_table, "FindMapping", each comparer(Text.ToList([ID]), Text.ToList([Search]))) in mapping
4 Replies
- AlienSx
Super User
Anonymous
let your_table = Table.FromRecords( {[ID = "1NA*W*", Search = "1NA2345W78"], [ID = "1NA????W*", Search = "1NA1234W78"], [ID = "1NA????W*", Search = "1NA12W78"]} ), wildcards = [#"?" = "0123456789", #"*" = "0123456789"], comparer = (x as list, y as list) => [x_first = List.First(x), a = Record.FieldOrDefault(wildcards, x_first, x_first), star = x_first = "*", b = List.First(y), par = Text.Contains(a, b), skip_y = par, skip_x = not List.AllTrue({star, par}), next = if List.IsEmpty(y) then "Yes" else if List.IsEmpty(x) or List.AllTrue({not par, not star}) then "No" else @comparer( List.Skip(x, Number.From(skip_x)), List.Skip(y, Number.From(skip_y)) )][next], mapping = Table.AddColumn(your_table, "FindMapping", each comparer(Text.ToList([ID]), Text.ToList([Search]))) in mapping - johnbasha33
Anonymous
Certainly! You can achieve this comparison and find matches using Power Query in Power BI. Here's a step-by-step approach:
1. **Load Your Data**: Import your data into Power BI, including the two columns you want to compare.
2. **Create Custom Columns**: You'll create two custom columns, one for each ID column, where you'll replace the placeholders with a specific character that represents a digit. Let's use "?" for "?" and "0" for "*".
3. **Compare Custom Columns**: After replacing the placeholders, you can compare the two custom columns to find matches.
Here's how you can implement this in Power Query:
1. Select the table in the Query Editor.
2. Go to the "Add Column" tab and click "Custom Column".
3. For the first column (let's call it "ID1_Fixed"), use the following formula to replace "?" with "1":
```
= Text.Replace([ID1], "?", "1")
```
4. For the second column (let's call it "ID2_Fixed"), use the following formula to replace "*" with "0":
```
= Text.Replace([ID2], "*", "0")
```
5. Now you have two new columns where placeholders are replaced with specific characters.
6. Add another custom column to check for matches. Let's call it "Match":
```
= if [ID1_Fixed] = [ID2_Fixed] then "Match" else "No Match"
```
7. You'll now have a column indicating whether there's a match or not.Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
johnbasha33 Thanks. But I have the replaced values with 0 and 1 only in the one column? In the Search Column I dont have ? and * for replace something?
- johnbasha33
Anonymous do same for search column too.
