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.
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"
Thank you so much Catalin.
Instead of join, I'd like to use a function (such as record.select or field.select), passing to the function a parameter and the parameter will be the product.
Is there any way?
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])
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?
