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 

 

 

= #table({"Craft","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}}))

 

Above is what I have in place, and using the actual data information but it tanks out.  Maybe I am referring to something incorrectly.

 

Below are the table nows from the Power BI datamodel as well as some sample data to show the column names also.  Have I mucked up a reference?

Thanks for the assistance.

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

Anonymous you miss a _ before {0}

 

  • 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.