cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
booj Frequent Visitor
Frequent Visitor

Merge multiple tables into one (iterate through combined values)

I've been banging my head against google and the search feature to figure out if there's a solution to this and I'm just using the wrong/inaccurate keywords. Any help pointing me in the right direction would be very helpful. I feel like there must be a relatively simple answer to this out there, but I'm new to Power BI.

 

I'm trying to run a sensitivity analysis. I have a number of projects, and I want to analyze how the financials of those projects (specifically internal rate of return or net present value) would look given a change in one criterion (specifically a variance to outlay cost). I have one table that is arranged with each row representing a project. I have a second table that has a distribution of potential variance in outlay cost (-30% to +30% staggered in 5% increments). I would like to create a third table that has a line for each combination of project and outlay cost variance. I am okay with the increase in the number of data rows that this would present.

 

The scaled down version of the question is that I have:

Table 1:

a

b

c

 

Table 2:

1

2

3

 

Table 3 (Desired outcome):

a,1

a,2

a,3

b,1

b,2

b,3

c,1

c,2

c,3

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
inetguru Frequent Visitor
Frequent Visitor

Re: Merge multiple tables into one (iterate through combined values)

In query window in table 1 add  column with formula = table 2. You will get all rows from table 2 for each row in table 1.

3 REPLIES 3
inetguru Frequent Visitor
Frequent Visitor

Re: Merge multiple tables into one (iterate through combined values)

In query window in table 1 add  column with formula = table 2. You will get all rows from table 2 for each row in table 1.

booj Frequent Visitor
Frequent Visitor

Re: Merge multiple tables into one (iterate through combined values)

Beautiful! Worked like a charm.

 

I've got a couple issues with paring down to unique values (my list of Project identifiers is not unique as intended), but I figure I'll do my research on that before I tax the goodwill of helpful people such as you.

inetguru Frequent Visitor
Frequent Visitor

Re: Merge multiple tables into one (iterate through combined values)

Contact me if necessary

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 942 guests
Please welcome our newest community members: