Forum Discussion

Cavring's avatar
Cavring
Icon for Helper I rankHelper I
9 years ago
Solved

Need help to shape data and how to approach data set

Hi

I have juste started learning Power BI and I would like to analyze my running race data. I have played around with may data, but I'm not getting my head around this.

 

Background:

  1. Each race data comes from a separate .CSV file
  2. Data typically include
    • Split number
    • Distance per split
    • Time per split

End goal:

Analyzing and comparing all my races by correcting my total time to official time.

 

Problem 1 -  How do I make it as generic and easy to and new data as possible?

  • My idea is to do most of the shaping of my data in the query
  • copy the "query macro" to new data I am adding
  • Name my each data set so I can combine them later

Does this sound like a good approach? How do I add a collumn whith the race name?

 

Problem 2 - correcting my total race time

My race data from my running watch does not match the offical race stats 100%.
I need to:

  1. Adjust my split times and distance, to correct the fact that I am running longer than the race distance
  2. Adjust my race time to the official time
    1. My watch and official may not be exactly the same
    2. I may stop, which is not taken into account on my watch

I don't know how to find how far extra I have run in the query?

I would also like manually add extra time to selected splits. How, when, where would I do that?

 

Excel data is here

Power BI data is here.

 

Here is an exampel og what I would like to do in Excel (gray is original data, green is shaped data):

Race 1 Additional offcial time spread out on all splitsRace 2 Additional offcial time spread out on two splits

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Cavring,

    You'd better create a new table unioning the required columns form tables using the formula below.

    Table = UNION(SELECTCOLUMNS('1610 Amsterdam maraton',"Løps ID",'1610 Amsterdam maraton'[Løps ID],"Split",'1610 Amsterdam maraton'[Split],"Tid",'1610 Amsterdam maraton'[Tid]),SELECTCOLUMNS('ManuellKorr',"Løps ID",'ManuellKorr'[Løps ID],"Split",'ManuellKorr'[Split],"Tid",'ManuellKorr'[Tid]))




    Then create a measure and table shown in screenshot2.

    Sum Tid1 = sum('Table'[Tid])




    Please download the file form here.

    Best Regards,
    Angelia

6 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Cavring,

    In order to help you to resolve your problem. I still need to comfirm the following information.

    1. In the first screenshot, what do you mean the Time,dist.Adj and Time,manual.Adj columns? where do you import? Based on my understanding, you add Time, Time,dist.Adj and Time,manual.Adj columns together and get the Time new column, right? If it is, you'd better create calculated column.

    2. The problem1 and problem2 is general, could you please descriobe it clearly?

    3. How you correct your total time to official time? Which column will be used? And what result do you want?

    Thanks,
    Angelia

    • Cavring's avatar
      Cavring
      Icon for Helper I rankHelper I
      9 years ago

      Hi v-huizhn-msft

       

      1. In screenshot 1 I have the gray data available (Split, Distance & Time). The green colloums are an example in Excel. I would like to this in Power BI, but I am not sure how. Can it be done in the query or if it is best to do it after the import.

       

      If I can add a 'Race name' in the query, I can do all the calculations after that. How can add this name (preferably based on the table name)?

       

      Screen shot from Excel. I need help with creating 'Race name'. Rows 36-42 shows what I would like to do in Power BI. The formulas in collumn 'D' explains what I would like to do.

       

      2. I can work on this a bit more myself before I aske for help. I think I know how I should approach it

       

      3. I think I will add a table nre with 'Official time' and 'Time manual adj.' and use relationships to connect the data through 'Race name'.

       

      Thanks,

      Espen

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Cavring,

        Please share the resource data for further analysis? So I can reproduce the scenario. Thank for understanding.

        For your question1, please add Race name by adding custom column as follows. You can select the add custom in Query Editor. In order to achieve the D column's result, you can create calculated columns.



        Best Regards,
        Angelia