Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Unsure how to tie this together with Power Query (multiple MAXIFS)

Hopefully I can explain this clearly 🙂   I am trying to come up with a Matrix visualisation that will show the current open positions we have.   In my data model I have the crew plan, which show...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    6 years ago

     

    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]}))

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

wdx223_Daniel   added that in but still 3 columns of Error

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 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}}))

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      wdx223_Daniel
      Icon for Community Champion rankCommunity Champion
      6 years ago

       

      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]}))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.