Forum Discussion

techmob's avatar
techmob
New Member
5 years ago

organizing data structure in a spreadsheet - examples

Hi guys

ok subject matter, i am pretty new to power bi and have been capturing data in a spreadsheet. i am having difficulting structuring this in power bi which i guess for more experienced users is quite simple

what i want to make sure is the capturing in excel is the correct way for power bi, i know power bi can structure it dependent on the users input, but before i get rocking i would like to just vet that aspect first

 

the project itself is about capturing football data from a sports video game. this video game does not output to any API or cloud service so is really all down to capturing that data at the end of the game. i am doing this by taking screen shots, was also looking at OCR etc, but that is a seperate subject

at the end of the game it presents the following information

 

 

i do have some data in a spreadsheet i have inputted manually.

the data to capture consists of the following

 

Overall team data

so i want to capture overall team performance, such as the amount of passes they have attempted, completed, shots etc. everything that is on that screenshot

 

and then the below image, is the invidual player who participated in this match, so i would like a history of every game they have placed with the below information in relation to them

 

 

then combined i can use power bi to present visuals for this data, i.e filter on overall team summary, look at individual player stats, compare players etc etc.

 

i want to make sure my structure is correct in excel, i have the following setup

1) setup - this is where player data can reference this for drop down list info

2) player data - this is where the individual player data is entered (see individual screen shot above)

3) team data - this is where the overall team data for that match is stored

 

i have a game ID associated in each of the player and team data tab, as they are relevant

 

hopefully you can see the screenshots below. i feel there is a better way of doing this and my research continues, but if a more experienced person can cast their eye over this and point me in the right direction i dont mind spending the time doing it (obviously) at the moment i am going no where fast

 

 

 

thanks

 

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You will want to read up on data models (star schema and snowflake) and normalization vs denormalization.

     

    The eventual solution depends entirely on your requirements - you could stuff all your data into a single wide table with lots of duplicated column contents, and it may work fine for a couple hundred entries.  Or you could go full object oriented and create individual tables for teams, events, seasons, locations etc.

     

    Capturing the structure in Excel is a mostly manual process. You could but tables in sheets, but it won't make much of a difference.  If you're comfortable with Excel, continue doing so.  Buf if you really want to dive into modelling then you want to use a proper modelling tool.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    Hi techmob ,

     

    It is not supported to directly get data from pes2020. However, you can consult the game manufacturer or forum to see if there is a file of match history. If the file is converted to an appropriate format, power bi can connect and visualize the data.

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Don_Alexis's avatar
    Don_Alexis
    Helper I
    5 years ago

    The game ID is essential that it is in all tables so that the ID (KEY) will connect at all, for greater handling of data molding it is necessary a good debugging and have lists in order to avoid being continuously debugging (I believe in my ina experience)

    Data Modeling.

    https://docs.microsoft.com/es-es/learn/modules/model-data-power-bi/2-how-to-manage-data-relationships

    there are quite a few graphs to exploit in the e-sport, few servers dare to provide this graphic statistics in detail of the denpeño of the pj and / user and offer its evolution, since it would bore your audience is better to be obstructed by radar CHART graph for the desenpeño of the player and / or a chord diagram with segmentation of data.

    I show you an example: perform a driver analysis and performance per truck, evaluating its handling with revolutions, the red frames is where you can tune according to the handling in which the finer the selection will show the right one or something.

    segmentacion de datos.png