PDA

View Full Version : Use data from Table in Power Query



Anzl
2016-09-21, 12:47 PM
Hi

I have a question about linking to data from a table, could not find a solution here on the side.
I want to search a String, but using the data from a table.
eg.

Text.Contains("[COLUMN]", "[Table in current workbook]")

Thanks

Dan Bliss
2017-05-26, 02:20 PM
The easiest way is to add a conditional column, on the "Add Column" tab in the General Section.
Here's an example:
I'll name my new conditional column "hasMyString"
if Column Name = Description contains mystring then output = TRUE otherwise FALSE

This creates a new Column, hasMyString, which can be filtered to show only the rows in which the mystring was found in [Description].

In M, this would produce the following:
#"Added Conditional Column" = Table.AddColumn(YourTable, "hasMyString", each if Text.Contains([Description], "mystring") then "TRUE" else "FALSE" )

If you have assigned the "mystring" value to a variable: myStringVar = "mystring", then M will accept:
#"Added Conditional Column" = Table.AddColumn(YourTable, "hasMyString", each if Text.Contains([Description], myStringVar) then "TRUE" else "FALSE" )

Usually YourTable is the result of the previously step in the query.