• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Post Grid using Power Query|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Post Grid using Power Query|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryPost Grid using Power Query
sp_PrintTopic sp_TopicIcon
Post Grid using Power Query
Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
1
March 3, 2020 - 12:45 pm
sp_Permalink sp_Print

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.  

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
March 4, 2020 - 8:51 pm
sp_Permalink sp_Print sp_EditHistory

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?

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
3
March 7, 2020 - 2:10 pm
sp_Permalink sp_Print

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.  

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
March 7, 2020 - 2:48 pm
sp_Permalink sp_Print

Ok, got the idea.

See the attached file.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
5
March 7, 2020 - 10:26 pm
sp_Permalink sp_Print

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)?  

 

  

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
6
March 7, 2020 - 10:34 pm
sp_Permalink sp_Print

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.  

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
7
March 7, 2020 - 11:45 pm
sp_Permalink sp_Print

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.  

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
March 8, 2020 - 12:05 am
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
9
March 8, 2020 - 12:14 am
sp_Permalink sp_Print

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.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
10
March 8, 2020 - 3:26 am
sp_Permalink sp_Print

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.    

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 62
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
11
March 9, 2020 - 12:34 am
sp_Permalink sp_Print

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.   

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
March 11, 2020 - 3:25 pm
sp_Permalink sp_Print

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?

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
13
March 11, 2020 - 4:15 pm
sp_Permalink sp_Print sp_EditHistory

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.

let
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"
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sharon Fletcher, Mark McCosker
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
jo moffitt
Michael Zandt
Ibraam Nasif
Gali Balmocena
Jocelyn Degran
Nurul Amalina
Sarah Mooney
Gopinath Gopalan
Nick Pape
Fakhar Zaman
Forum Stats:
Groups: 3
Forums: 24
Topics: 6347
Posts: 27751

 

Member Stats:
Guest Posters: 49
Members: 32305
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.