Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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"
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"
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 😄
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |