Hi,

Something does not look ok:

In grid example sheet, you have in B1 the Team name as header, then in round 1 (cell B2) for the first team you have the player name for that team.

What should be in round 2, cell B3, knowing that B2 is the player name for B1 team, for round 1?

It's a "grid" of players taken by each team, each round.  Thus, in sheet "Grid Example," what would show in Round 2, cell B3, would be the player selected by team Runin' Grundy's in Round 2, Pick #40.  It's to post players to the "grid" as selected. 

I have it working.  It's just a bunch of merges and I thought there would be a more efficient way I was not thinking.  

Ok, got the idea.

See the attached file.

I appreciate.  However, if you do not mind, in trying to "learn" here, I'm with you but two steps:

The merged query, I'm not familiar with the Table.NestedJoin; that I'll have to explore to learn more.  It appears it could in handy.  I see how it worked but not fully understanding.  It's as if you merged with only one table.      

And, the custom column, that one, you got me; I'm not following that.  Though I see the output from it, the steps I could not figure out.  I'll research.   

One last question.  If you look at the original file, "Grid Example" sheet, is there an easy way to incorporate the first column (i.e. the round)?  

Let me clarify that last question about adding the "Round" column at the beginning.  I know I could add a step and Column Index would accomplish this.  I just wondered if instead of adding another step or two, could it be incorporated into the one of the already executed steps.  

And, I know have a better understanding of the custom column.  Basically joined all the players, then separated into columns with the "|" character.  Got it.  

See attached updated file.  Would the same procedure here hold true for the "Roster Grid?"  As you can see, I have a ton of queries to achieve what you have completed in far less steps.  

Basically, after we remove duplicates and we have a list of unique team names, you can click the merge button from pq editor, and merge the table with itself, with column Team selected in both tables.

I highlighted in red below the changes i MANUALLY made after performing the merge using pq editor ( those texts in red was #"Removed Duplicates", because I choose to merge the table with itself, but I need to merge the unique list of teams with the initial table, to get all players for each team)

The custom column: Each table in Players column has all the columns from the initial table and only the rows matching the current team. We need only the Players column, that's why I removed all other columns with: Table.SelectColumns([Players],{"Player"}) and combined the column values into a single text string.

Now I realize that it was not really necessary to remove all other columns, we can still pass only one column to Text.Combine:

Text.Combine([Players][Player],"|")

The Combine operation is important because it defines the structure needed, Transpose will just rotate the matrix.

For Round number, it's best to add an index column as the last step.

ExcelNovice2020 said
See attached updated file.  Would the same procedure here hold true for the "Roster Grid?"  As you can see, I have a ton of queries to achieve what you have completed in far less steps.    

Seems to be slightly different, but I don't see why you have similar rows like 9-10-11, (only 1 with data), 14-18 (again only first row with data).

Same query can be used, but to get the Pos column, you have to add an index column and merge the result again with the initial table, from the resulting column of tables keep only the Pos corresponding value.

Ahh, good question.  I did not clarify.  That's because the "OF" position, each team has to carry 4.  Another example would be the "SP" position; each team is required to carry a minimum of 5 and so forth.  As a player entered to "Draft Results," the appropriate player is then populated over to the "Rosters" sheet at their appropriate position and team.   

In my example file, I have only posted a couple of players.  I have uploaded a new file, showing another example using team Runin' Grundy's where I've added more than one player, and, at the same position.    

Catalin Bombea said

Seems to be slightly different, but I don't see why you have similar rows like 9-10-11, (only 1 with data), 14-18 (again only first row with data).

Same query can be used, but to get the Pos column, you have to add an index column and merge the result again with the initial table, from the resulting column of tables keep only the Pos corresponding value.  

Okay, I'll admit defeat.  I could not figure out the "Same query can be used, but to get the Pos column, you have to add an index column and merge the result again with the initial table, from the resulting column of tables keep only the Pos corresponding value."

It's just beyond my level of understanding at this point as I just cannot get it.   

Still not clear if those details will be set in Draft Result, or we have to setup the grid to have an exact number of OF, SP, RP and whatever is needed.

I see that you have a Positions query, that's the one that defines these details, right?

You can try this query. It's strictly based on your data, will list only teams where players are assigned. If the same team has 7 OF in the data, they will be displayed, even if the rule says no more than 4 OF in a team.