Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, Im trying to search values in one column for values in another column in another table and then returning a corresponding column value.
Lets say I have two tables, A and B.
A contains the values I want to search, there could potentially be hundreds of thousands of values in here.
B contains a list of values I want to search for and another corresponding value column, there will most likely be hundreds of values in here.
Here is an example of these tables:
Example Table A:
Values to be searched |
Fast cars |
toy blue cars |
remote control cars |
Red to car |
blue cars |
green cars |
other cars |
great red cars |
Example Table B:
Value to search for | Corresponding value |
red car | Red |
green car | Green |
blue car | Blue |
I essentially want to add a column in table A in query editor so that the output looks like this:
Values to be searched | Matched Value |
Fast cars | |
toy blue cars | Blue |
remote control cars | |
Red toy car | |
blue cars | Blue |
green cars | Green |
other cars | |
great red cars | Red |
And as it could be working across hundreds of thousands of values to be matched I want to make it as efficient as possible.
Can anyone point me in the right direction here please?
Thanks in advance!
Solved! Go to Solution.
Here is a custom column formula. it works with sample data
See file attached as well
=let myvalue=[Values to be searched] in Text.Combine( Table.SelectRows(TableB, each Text.Contains(myvalue,[Value to search for]))[Corresponding value] , ",")
Here is a custom column formula. it works with sample data
See file attached as well
=let myvalue=[Values to be searched] in Text.Combine( Table.SelectRows(TableB, each Text.Contains(myvalue,[Value to search for]))[Corresponding value] , ",")
Hello @Zubair_Muhammad ,
I am trying similar but for a single column output, Would be great, if you could help me out. Here is the snapshot of my query.
Thanks
@Zubair_Muhammad
appreciate this is an old one but stumbled across your code and it's so close to something I'm trying to achieve so hoped you may be able to help.
I'm searching Table 1 field [Description] for values in a column called [Tag] from a table called Symptom.
=let myvalue=[Description] in Text.Combine( Table.SelectRows(Symptom, each Text.Contains(myvalue,[Tag]))[Tag] , ",")
I'm looking for something which does that, but only returns the first hit rather than all with comma separation.
For bonus appriciations my next step is to return the value in the second column of the Symptom table....
Table is simple 2 columns:
Tag Output
Red It's red
So if I was searching a decription field value of "Is it red or orange". It would look for the value 'Red' from the tag field and return 'it's red' from the output field. If that's can't be done in power query, no biggie I'll just do a lookup in DAX.
Hi,
This is an old thread. Share some data, describe the question and show the expected result.
@Zubair_Muhammad You are a lifesaver. This behaves just like a vlookup in Excel. I was driving myself crazy trying to figure this out.
Question: the way I did this was by merging the tables first and then expanding the relevant columns.... is that an unnecessary step? As long as the table is connected to within the same PBIX file can I just referance the column in the other table for the lookup?
Thank you and As Salaam Alaikum
@Anonymous check this out
Hi @Anonymous
Wa alaikumus salam,
Merge using "user iterface of Power Query" is always the best way to proceed.
In above case merge was not possible. That's why we had to use a custom formula
Thanks Zubair, seems a bit nicer illustrated and compact. As mentioned though still super heavy but thats PBI I think so using other routes now
Hi @murrayb8,
Please see the M code below two tables.
// search let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkpNUUhOLFLSUQpKTVGK1YlWSi9KTc2DirmD2GDRpJzSVKigE5CpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Value to search for" = _t, #"Corresponding value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value to search for", type text}, {"Corresponding value", type text}}), #"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"Corresponding value", Text.Lower, type text}}) in #"Lowercased Text" // table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYy7DYAwDAVXsVIzBwNQ0EQUJnkiBWDJNgXbE0IRUd69T4xh5v2CkQutIANrKshhGWIY2ZwSqzVyuWmt1W4Uh3hlOV1l735CprddReP/alPg7CheoL+UnbQ+fGp5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1), #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Values to be searched", "Values to be searched - Copy"), #"Lowercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Values to be searched - Copy", Text.Lower, type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Lowercased Text", "Values to be searched - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Values to be searched - Copy.1", "Values to be searched - Copy.2", "Values to be searched - Copy.3"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Values to be searched", "Index"}, "Attribute", "Matched Value"), #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"Matched Value"}, search, {"Corresponding value"}, "searchFor", JoinKind.Inner), #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Index", "Matched Value"}), #"Merged Queries1" = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Other Columns", {"Index"}, "Removed Other Columns", JoinKind.LeftOuter), #"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Removed Other Columns", {"Matched Value"}, {"Matched Value"}), #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Removed Other Columns",{"Values to be searched", "Matched Value"}) in #"Removed Other Columns1"
Hope this helps.
Mariusz
Thanks Mariusz, I had a go at this and I think the columns were mixed up in the merged query step. I got there in the end though but it toolk hours to refresh as the text searching seems to be super slow. Found a way to run the same through a VM though so will be going that route from now on!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |