I would probably add a manual "count" column with a value of 1, and then pivot the number of days between games column to give you a count of how many each team has, leaving you with a table that looks like this: Team Add a column subtracting the two, and you have the number of days between games.įrom here you can leave some stuff up to your discretion. Expand out the new column, and only keep the date column.The first query we are going to use the team name and master index as the columns to match, and then we are going to merge it against team name and second index. Now we are going to merge the query against itself, basically to shift the date column up a row so that we can see how many days are between each team's game.Uncheck use original column name as prefix, and press OK to expand your tables out. Select the AllRowsSecondIndex column and select remove other columns, then click the outward facing arrows at the top of the column to expand it.Same thing, press the fx button and enter this = Table.AddColumn(#"Added Custom", "AllRowsSecondIndex", each Table.AddIndexColumn(,"Second Index",0,1,Int64.Type)) Now we will add a second index to the subgroupings.You should be able to click the fx button on the formula bar and enter this, so long as the last step was grouping the rows and all names are the same = Table.AddColumn(#"Grouped Rows", "AllRowsIndex", each Table.AddIndexColumn(,"Master Index",1,1,Int64.Type)) This gives us each team's game number in order, so that we can reference the dates between rows. New column name called AllRows, operation is All Rows. Delete your attribute column, and select your Value column and from the Transform tab select Group By.You should have a Date column, an Attribute column which has TEAM HOME and TEAM AWAY, and a Value column which lists your team names. Select your date column and from the transform tab select unpivot columns - unpivot other columns.Load your data into power query, assuming all games will be regular season I deleted that column.There is a way that could help, but you'll still need to do some manual scheduling changes. They are identifiable with a special user flair.Ī community since MaAsking a question? Describe if you are using Excel (include version and operating system!), Google Sheets, or another spreadsheet application. Occasionally Microsoft developers will post or comment. Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors Date Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data. NOTE: For VBA, you can select code in your VBA window, press Tab, then copy and paste that into your post or comment. To keep Reddit from mangling your formulas and other code, display it using inline-code or put it in a code-block This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |