Forum Discussion
Unsure how to tie this together with Power Query (multiple MAXIFS)
Anonymous
= #table({"Craft","Max_Count","PeakMonth","PositionsToFill","Location"},Table.ToList(ActiveStaff, each let a=Table.Sort(Table.SelectRows(CombinedCrewPlans,(x)=>x[Craft]=_{0}),{"Count",1}){0}? in if a=null then {_{0},null,null,null,null} else {_{0},a[Count],a[Date],a[Count]-_{2},a[Location]}))
Community Champion
Anonymous
= #table({"Craft","Max_Count","PeakMonth","PositionsToFill","Location"},Table.ToList(ActiveStaff, each let a=Table.Sort(Table.SelectRows(CombinedCrewPlans,(x)=>x[Craft]=_{0}),{"Count",1}){0}? in if a=null then {_{0},null,null,null,null} else {_{0},a[Count],a[Date],a[Count]-_{2},a[Location]}))
wdx223_Daniel amazing thanks. I have made a mess of location somewhere as it isn't splitting out the sites for Sydney and Vancouver but I will figure that one out.
Greatly appreciate the time spent with this one.
Cheers.
wdx223_Daniel coming unstuck with the location side of things.
The ActiveStaff shows the current count per NSW or BC (the location). I have a dim table with two columns LocationLong, LocationShort which Maps Sydney to NSW and Vancouver to BC. I have this all the way through tables due to the differing sources so that should be fine.
The CombinedCrewPlan shows two maximum values for each craft. For Animation the Max is 49 - 31/05/21 (Sydney) and 59 - 01/03/21 (Vancouver).
The output is showing two lines for Animation with the correct PositionsToFill number, but the PeakMonth and Max_Count are only showing for Vancouver - so 59 on both lines and 01/03/21. Should the location be coming from the ActiveStaff table instead of the CombinedCrewPlan one?
- wdx223_Daniel
Anonymous could you provide some sample data, and show what result you want?
wdx223_Daniel There are three tabs, the first two represent the tables and the last is the output
https://drive.google.com/file/d/1xTcgKcBt3IiLsfukLtgGaETlto7G8HDp/view?usp=sharing
- wdx223_Daniel
sorry, i can not reach the file you provide. Anonymous
wdx223_Daniel message me your email address and I will flick it over to you.