
Active Member

October 18, 2019

Hi everyone, which is the formula that given a table like
PRODUCT |
QUANTITY |
A |
10 |
B |
20 |
C |
15 |
Allow me to pass the product as parameter so to have the quantity as result So if for example, I pass “B” as parameter the formula give 20 as result. Thank you very much for the help.


November 8, 2013

You should merge the data table with that Loookup table you described above, I guess you have a Product column in your data table, use the Product columns from both tables as keys.
Then you just have to expand the Quantity column.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"Product"}, LookupTable, {"Product"}, "LookupTable", JoinKind.LeftOuter),
#"Expanded LookupTable" = Table.ExpandTableColumn(#"Merged Queries", "LookupTable", {"Quantity"}, {"Quantity"})
in
#"Expanded LookupTable"


November 8, 2013

It is possible, but it will be slower, is there a reason for that preference?
You can convert the lookup table to a custom function, replace the name of the query with: LookupTable (this will be the name of the new function).
(Product)=>
let
Source = Excel.CurrentWorkbook(){[Name="LookupTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Quantity", Int64.Type}}),
#"Filtered Rows" = try Table.SelectRows(#"Changed Type", each ([Product] = Product)){0}[Quantity] otherwise null
in
#"Filtered Rows"
In the Data query, you can add a new column with a simple formula using the new custom function, passing the product as a parameter:
=LookupTable([Product])

Active Member

October 18, 2019

The problem that I’m trying to solve sound like:
Estimate the quantity sales of product via the quantity sales of other product.
So If I have a table for sales like:
PRODUCT | QUANTITY |
CAR | 10 |
And I know that for each CAR sold I will sell also 4 TYRE and for each TYRE 4 BOLT
So I have a table like:
PRODUCT | PRODUCT RELATED | RULES |
CAR | TYRE | *4 |
TYRE | BOLT | *4 |
I want to produce another table like
PRODUCT RELATED | QUANTITY |
TYRE | 40 |
BOLT | 160 |
Is there a solution in PQ?
1 Guest(s)
