Creating a flexible set of goals

[cmcreynolds]

Hello again -

I work in education. I have one table of various schools (our clients) and their degrees. I have another table that lists various start terms within a fiscal year. Is there a way to merge these (via Power Query, Power Pivot, or good ole plain Excel) so that I can:

1. adjust for new clients and programs
2. adjust for new start dates (I'm working on FY 2017 and we don't have all the dates, yet)
3. add our goals to these "outputs"

So, for example, School A has two degrees: Biology and Chemistry and both of those degrees have six start dates in a year, but we only have, let's say 4 of them. Can I create a table from the other two that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client
[/TD]
[TD]Program
[/TD]
[TD]StartDate
[/TD]
[TD]Goal1
[/TD]
[TD]Goal2
[/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Bio
[/TD]
[TD]1/1/2016
[/TD]
[TD]*I add this data manually
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Chem
[/TD]
[TD]1/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Bio
[/TD]
[TD]3/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Chem
[/TD]
[TD]3/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Bio
[/TD]
[TD]6/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Chem
[/TD]
[TD]6/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Bio
[/TD]
[TD]8/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SchoolA
[/TD]
[TD]Chem
[/TD]
[TD]8/1/2016
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And will it allow me to add additional programs (or even clients) in the one table and populate it with corresponding start dates?

OR even add an additional start date?

I have unique identifiers for each row in both tables. I'm just having a time conceptualizing how to come together (like a database). I suppose I can build a simple Access database? *le sigh*

Thanks for your help.

 

[cmcreynolds]

Oh, and this wouldn't need to be ongoing past the time I submit the budget. So, it won't be "grabbing data" constantly or anything that fancy. I just want it flexible enough so I don't have to add (# of programs) x (# of start dates) for each client

 

[Matt Allington]

You can certainly do this in Power Query, but it is hard to give you specific guidance without seeing the exact tables. Take a look at this blog post I recently wrote - it may give you some inspiration.

Cross Join with Power Query -


Also look at the Merge feature and Group By features on the menu in Power Query.

 

[cmcreynolds]

Cool, that was "sort of" what I wanted to do, but what I ended up doing was a series Data Validations - cascading, even. So, instead of using complete tables (like my thought was if I were to Use Access), I just had some lookup lists - and that satisfied what I needed for this project.

 

[Matt Allington]

Yes I didn't think it was exactly what you wanted, but I felt that it was probably close enough for you to get an idea of how to do what you wanted.

 

[cmcreynolds]

BUT, it is a great idea for a future project of mine...is there a way though to expand only if there are matches with another field? What I did was expand, then created a dummy code, then filtered. But if there are enough rows, won't it get enormously (needlessly, perhaps?) huge before I can filter? Just wondering about that.

 

[Matt Allington]

PQ only loads a sample of data into the UI. You can filter based on that sample and then during processing it works over the entire file. I normally filter out NULL based on one of the joined columns - this gives you only the joins. You can also do a merge query with Inner Join as the join type. That will give you only the matches - which sounds like what you are after. You need a recent version of PQ to have the inner join features