Forum Discussion
Comparing col start/ prefix to another tbl and returning corresponding row
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.
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!!!!
2 Replies
Here is one method to try...
If you have a prefixTableand 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.
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!!!!