Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Adding column from another table based on certain criteria

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)

TamaraD_0-1646183933737.png

 

Table 2 (containing the required capacity)

TamaraD_1-1646183984643.png

 

Table I want to create:

TamaraD_2-1646184023544.png

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.

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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
Not applicable

Hi Nate,

 

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

TamaraD_0-1646194010657.png

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
Not applicable

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

TamaraD_0-1646194346258.png

Then I get:

TamaraD_1-1646194468892.png

 

How do I pivot to get the final result?

 

Anonymous
Not applicable

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
Not applicable

I got the grouping bit, it looks like this:

 

Table 1 (Available):

TamaraD_2-1646192461243.png

 

 

 

Table 2 (required)

 

TamaraD_1-1646192436284.png

 

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. 

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

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
Anonymous
Not applicable

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

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors