Forum Discussion
Power query division without merging tables?
Hi, I have this calculated column in report view:
I do need to recreate it using power query since I need to use it create other column.
I dont know is it possible to Value.Divide when tables are not merged, I tried but could not make it work.
Anyone know if it is possible or I have to do a merge?
Justas4478 Hi! If you don't want to merge, you can look up values from the second table dynamically, something like:
Table.AddColumn(#"Previous Step", "Calculated Column", each
Value.Divide(
[Absolute Difference Quantity],
List.First(
Table.SelectRows(
#"Product information",
each [Product ID] = _[Product ID]
)[Pick to Base UoM Quantity],
null
)
)
)If performance is a concern and you want a cleaner approach, merging tables is the best way to go. If merging is not an option due to constraints, then the Table.SelectRows method can work but will be slower for large datasets.
BBF
2 Replies
Justas4478 Hi! If you don't want to merge, you can look up values from the second table dynamically, something like:
Table.AddColumn(#"Previous Step", "Calculated Column", each
Value.Divide(
[Absolute Difference Quantity],
List.First(
Table.SelectRows(
#"Product information",
each [Product ID] = _[Product ID]
)[Pick to Base UoM Quantity],
null
)
)
)If performance is a concern and you want a cleaner approach, merging tables is the best way to go. If merging is not an option due to constraints, then the Table.SelectRows method can work but will be slower for large datasets.
BBF
BeaBF The performance concerns is my primary worry so I will try the merge in hopes that it is better over long term.
Thanks for the solution I know I will find where to use it.
