Forum Discussion
Adding column from another table based on certain criteria
I worked it out with a pivot in excel via power pivot. It's now showing as needed and I get it to automatically update as it's done by power pivot.
Thanks heaps!
Thanks for the reply, I'm definitely going to give it a go.
Questions regarding your answer:
- What do you mean with the ' o e table' ?
- What do you mean with the pivot functions? as in a pivot table?
(I am creating a summary table as I need to create automatic updating graphs from it and it can't create graps over muliple tables with mulit relationships)
Thanks heaps.
Sorry, that was supposed to say "other table. Basically the same steps for both tables, which is group by month name and WC, and choose the Summ aggregation on the columns that you want summed. Then you can use either a left or inner join depending on your needs, using the Month Name and WC as the join columns. If you use Table.Join, with the JoinKind Inner parameter, you will end up with rows that look like:
Month Name | WC | Sum of Available Capacity | Sum of Required Capacity
But since your desired output requires not a row for each WC, but two columns for each, you will therefore need to pivot the WC column (Table.Pivot), so that you can use those values as headers, and the sums as the values. At that point, you should have what you need. This is all using the standard GUI functions.
--Nate