Forum Discussion
Solved
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, P...
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"
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"