Forum Discussion
If Statement with Vlookup
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!
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"
5 Replies
Hi brysonb12 ,
is it ok in Power Query?
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 😄
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.
I cannot click the link for some reason. Is there a formula that can just be made wihtin the column to get this?
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"
