Creating a new table based off of multiple conditionals

[srpeters]

Hello,
 
I have a table with a lot of information I need and another with only one column I need. My problem is that there are a lot of duplicate values in both tables and there does not seem to be a way for me to remove these duplicates without messing up the correct data. I was wondering if there was a way to make a new table that only pulls in a column if it passes three different conditionals. For example, lets say I have a table about animals and there were these columns of data: Name, color, type, breed, and location. Then a second table with Name and Amount for its columns. Could I make a new table in Power Query that only included animals with Names and Amounts (from the second table) where the color is 'blue', the type is 'mamal' and the 'breed' is 'shorthair'(from that first table)? 
 
Thank you in advance!

[dufoq3 · Accepted solution]

Hi @srpeters, 

for future requests don't forget to provide sample data as table and also expected result (based on sample data).

I'm not sure if you want this exactly:

t1

t2

Result

Just replace t1 and t2 codes with your table references.

You can specify conditions here:

let
    t1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY1BCoMwEEXvkrWXEGk3pVCaumnIYloHIyaTMkaKt3cSFKF0M5/5PN43RtXUMX5VpV5+RokrBPCSk4ucJFutbGWk5jSQ/Iyd3JPHjwPKgI/U56bdORwPW9hsG3S+F+iGCVneyED9v1F9KVzjeJhkFooQ3lkcIo24HMrmWVCNXEw/u7uwfihrVw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Color = _t, Type = _t, Breed = _t, Location = _t]),
    t2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLKUotV9JRMjRQitWJVgpILUktAnKNIFznjKLM4pLMvESgkDFEKDi1KD0VyDWBcH0Ti1KzgVxTOBeoHMg3A/JjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Amount = _t]),
    // Enter as lower text!
    Conditions = [ Color = "blue",
    Type = "mamal",
    Breed = "short" ],
    Ad_t1 = Table.AddColumn(t2, "t1", each Table.SelectRows(t1, (x)=> Text.Lower(x[Color]) = Conditions[Color] and Text.Lower(x[Type]) = Conditions[Type] and Text.Lower(x[Breed]) = Conditions[Breed]){[Name = [Name]]}?, type table),
    FilteredRows = Table.SelectRows(Ad_t1, each [t1] <> null),
    RemovedColumns = Table.RemoveColumns(FilteredRows,{"t1"})
in
    RemovedColumns

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...