Adding column from another table based on certain criteria

[Anonymous]

Hi all,

I am looking to make a new summary table in excel power query (showing the available capacity and required capacity per wc per month), containing columns from other tables based on certain criteria's, but I can't seem to get it working properly.

Can anyone please help?

Table 1 (containing the available capacity)

Table 2 (containing the required capacity)

Table I want to create:

So what I'm after is an overview table of the available and required capacity per WC per month.

This works, but is way too slow as it loads the full table per added column and then filters out what I need based on the criteria:

Click on add custom column in the power query editor and type in the window:

List.Sum(Table.SelectRows(
Released,
(InnerTable) => InnerTable[Month]=[Month Name] and
InnerTable[WC]= "Q"
)[Remaining Run Time])

Is there another way to do this? (e.g. with just adding a column?)

Many thanks.

[Anonymous · Accepted solution]

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!

[Anonymous]

Hi Nate,

Thanks heaps, I got now got both columns selected and if I merge I get this:

The left join seems to make more sense than the inner, as the inner has less rows. 

As I now have a table on the right, what do I do next?

[Anonymous]

I think I need to do sum, then I get:

Then I get:

How do I pivot to get the final result?

[Anonymous]

Is it correct that I have to pivot outside of power query, in excel itself? Or is there a way to do it in power query?

[Anonymous]

I got the grouping bit, it looks like this:

Table 1 (Available):

Table 2 (required)

Got issues with joining. If I choose to merge queries, I can choose 2 tables. I choose table 1 and table 2. Then click on the column that is the same in both (month). I can't seem to select both month and wc. 

[Anonymous]

Hold the CTRL key when selecting your second set of columns.

--Nate

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

I would use the group function from the GUI, grouping on Month Name and WC, and choose the Sum aggregation on Available Capacity on the o e table, and do the same type on the other table, but sum the Required capacity. Then you can Join the two tables on the Month Name and WC fields. Then you can use the Pivot functions to transform it all into the columns you want to see.

--Nate