Forum Discussion
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
- inetguru
Advocate I
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.
- boojFrequent 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.
- inetguru
Advocate I
Contact me if necessary