Forum Discussion
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...
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
Community Champion
Community ChampionAnonymous please try this M code
= #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
Anonymous you miss a _ before {0}
wdx223_Daniel added that in but still 3 columns of Error
- wdx223_Daniel
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}}))