Forum Discussion

themitchells16's avatar
themitchells16
Copper Contributor
Mar 16, 2017

Makine a table from a bunch of Data

Hello Community. I have a quick question to ask the community. I have a data set (attached below) I'm trying to answer a few questions. i need to find a way to make a table that counts the number of times each player_name and team_name had an offensive rebounds ('O' in the event_desc_id) that led to a 3FGA, 3FGM, 2FGA, directly after (row below) Thank you for any help.  Let me know if you don't understand I'll try and make it easier . Let me know if i can clear it up anymore

BI & Data Analytics
Tutorial
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 16, 2017

    Hi,

     

    Perhaps easiest way is to use Get&Transform.

     

    If i understood your logic correctly. Name your first column as ID (in A1), when generate the query on your data (stay on any cell within it, when Data->From table),

    edit the query and change the code on

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
	// Add Column with the event from next row
	AddEventFromNextRow = Table.AddColumn(Source,
		"Custom", each Source[event_desc_id]{[ID]+1}
	),
    FilterEvents = Table.SelectRows(AddEventFromNextRow,
		each ([event_desc_id] = "O") and (
			[Custom] = "2FGA" or
			[Custom] = "3FGM"
			)
	),
    GroupByTeamPlayer = Table.Group(FilterEvents,
		{"team_name", "player_name"},
		{{"Count", each Table.RowCount(_), type number}}
	)
in
    GroupByTeamPlayer

    assuming the name of the table is Table1 (not all filetrs are in the query above, you may add) . Load the query into the new table.

     

     

     

     

Resources