Forum Discussion

booj's avatar
booj
Frequent Visitor
9 years ago
Solved

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!

  • 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

  • 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's avatar
      booj
      Frequent Visitor

      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.