Log In
Register
Home
Active Member
October 18, 2019
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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"
Active Member
October 18, 2019
Offline
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
Offline
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)
