If Statement with Vlookup

[brysonb12]

Looking for some help to see if someone can assist with a if statement with a vlookup I am trying to return. I have the follwing

Table 1 - Company 1 (Walmart)

Column 1 -Identifier (Cups, Bags, Pens, Pencils, Folders, ect.)

Column 2 - Rates

Table 2 - Company 2 (Amazon)

Column 1 -Identifier (Cups, Bags, Pens, Pencils, Folders, ect.)

Column 2 - Rates

Table 3 - Audit Data

Column 1 - Company ( I have multiple)

Column 2 - Identifier (Cups, Bags, Pens, Pencils, Folders, ect.)

Column 3 - Quantities

I want to create a column within Table - 1 that is a IF statement with a Vlookup. Something along the lines of this.

=If(Table3(Company)="Walmart",

Vlookup(Table1(Identifier),

Table3(Column2(Identifier):Table3(Column3(Quantities),

Column3(Quantities),

Then I want it to be able to take the Quanties and multiple it by the rates value in Table1

Hopefully someone can assist!

[mangaus1111]

Hi @brysonb12 ,

see my solution in the pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJiHfRIaja1FReNxxK?e=ktvleW

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brysonb12]

I cannot click the link for some reason. Is there a formula that can just be made wihtin the column to get this?

[mangaus1111 · Accepted solution]

Hi @brysonb12 ,

below there is the formula for Table3, but first you have to create 2 new columns, one in Table1 and the other in Table2. with the name of each table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lFyLi0oBlKGSrE6KOJOiekgcSN08YDUPJC4MbK4EQ5zjHCYY4RiTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Identifier = _t, Quantities = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Identifier", type text}, {"Quantities", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Company", "Identifier"}, Company1, {"Company", "Identifier"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Rates"}, {"Changed Type.Rates"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Changed Type", {"Company", "Identifier"}, Company2, {"Company", "Identifier"}, "Company2", JoinKind.LeftOuter),
    #"Expanded Company2" = Table.ExpandTableColumn(#"Merged Queries1", "Company2", {"Rates"}, {"Company2.Rates"}),
    #"Inserted Maximum" = Table.AddColumn(#"Expanded Company2", "Rate", each List.Max({[Quantities], [Changed Type.Rates]}), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Maximum",{"Changed Type.Rates", "Company2.Rates"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Result", each [Quantities]*[Rate]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Result", type number}})
in
    #"Changed Type1"

[Anonymous]

Hello @brysonb12!

You can create a colum in the table 1 and table 2 with the company name, then you create another colum in all 3 tables concatening the company name and identifier... Then you'll get a primary key in each... Then you decide if you want to use lookupvalue or related 😄

[mangaus1111]

Hi @brysonb12 ,

is it ok in Power Query?