Log In
Register
Home
February 8, 2020
Offline
I'm trying to create a "grid" using power query. I can do what I'm trying to do via VLookup but I'm trying to learn perhaps a better way through power query without a ton of query merges or appends. I've attached the example worksheet. In a sense, as data is entered into column D, sheet "Draft Results," it will then populate the "grid" as shown on sheet "Grid Example." Note, the sheet "Grid" is my practice query that I could not figure out how to do what I was trying without a ton of mergers and appends. The "Grid Example" is just an example of what I'm trying to accomplish through power query. Thanks.
November 8, 2013
Offline
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?
February 8, 2020
Offline
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.
November 8, 2013
Offline
Ok, got the idea.
See the attached file.
February 8, 2020
Offline
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)?
February 8, 2020
Offline
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.
February 8, 2020
Offline
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.
November 8, 2013
Offline
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)
let
Source=Excel.CurrentWorkbook(){[Name="Draft_Results3"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Rd] <> "Rd") and ([Team] <> null)),
#"Removed Duplicates" = Table.Distinct(Table.SelectColumns(#"Filtered Rows",{"Team"}), {"Team"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"Team"}, #"Filtered Rows", {"Team"}, "Players", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Text.Combine(Table.SelectColumns([Players],{"Player"})[Player],"|")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Players"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18", "Custom.19", "Custom.20", "Custom.21"}),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"
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.
November 8, 2013
Offline
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.
February 8, 2020
Offline
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.
February 8, 2020
Offline
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.
November 8, 2013
Offline
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?
November 8, 2013
Offline
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.
Source=Excel.CurrentWorkbook(){[Name="Draft_Results"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Rd] <> "Rd") and ([Team] <> null)),
#"Removed Duplicates" = Table.Distinct(Table.SelectColumns(#"Filtered Rows",{"Team"}), {"Team"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"Team"}, #"Filtered Rows", {"Team"}, "Players", JoinKind.LeftOuter),
Transform = Table.TransformColumns(#"Merged Queries",{"Players",each Table.SelectRows(_, each [Player]<>null)}),
#"Added Custom" = Table.AddColumn(Transform, "Custom", each Text.Combine(Table.CombineColumns(Table.SelectColumns([Players],{"Player","Pos"}),{"Player","Pos"},Combiner.CombineTextByDelimiter(":"),"Player:Pos")[#"Player:Pos"],"|")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Players"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns", {{"Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.1", type text}, {"Custom.2", type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Custom.1] <> "")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Custom.2", "Pos"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Team]), "Team", "Custom.1",each Text.Combine(_,"#(lf)"))
in
#"Pivoted Column"
1 Guest(s)
