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 ChampionAnonymous you still miss a field name of Max_Count
= #table({"Craft","Max_Count","PeakMonth","PositionsToFill"},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} else {_{0},a[Count],a[Date],a[Count]-_{1}}))
wdx223_Daniel wow thanks, that was a pretty big miss.
I still have 'Error' in the PositionsToFill column. Also, if I wanted to add Location to this table from CombinedCrewPlans where do I add that in. Struggling to understand the column populations with this one, but am trying to disect the command. Appreciate your assistance with this one, it is a great learning exercise for me seeing this unfold.
S.
- wdx223_Daniel
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?