Comparing col start/ prefix to another tbl and returning corresponding row

[jansse]

Hi, I am fairly new to Power Query but capable of managing my self with basics. Now I received challenge with what I dont have any faintest Idea how to approach it. Could you please help me abit?

So... I am having col with values and would need to compare the start of this col to another tbl prefix col and if prefix matches returning a new col with description.

[jgeddes]

Here is one method to try...
If you have a prefixTable

and a factTable

You can add a column to the factTable with the following code...

= Table.AddColumn(PREVIOUSSTEP, "prefixFound", each try let rowValue = [valueToCompare] in List.Select(prefixTable[prefix], each Text.StartsWith(rowValue, _)){0} otherwise null, type text)

and end up with the result...

From there you can merge into your prefix table to get the description.

Hope this helps.

[jansse · Accepted solution]

With this I wasnt all the to finish but almoust. This gave me value from the prefix col and not from the description col. I found on another topic thing that i was able to compose as a solution by using your suggested solution

= Table.AddColumn(PREVIOUSSTEP, "prefixFound", each List.First(Table.SelectRows(tblImaginary (insideTbl) => Text.StartsWith([valueToCompare], insideTbl[prefix]))[Description]), type text)

So... in other words I wouldn't have been able to get this with out you!! Thanks you!!!!