Forum Discussion
Helper INeed help to shape data and how to approach data set
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
Microsoft EmployeeHi 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
Helper I
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
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- Cavring
As for Race name I have soveld it by adding a custoum columns as you say. I enter i Run ID and make a relationship to another table with Run ID and Race name.
I have now come to a problem I can't solve. This is my formula:
Tid final = CALCULATE(sum('1610 Amsterdam maraton'[Tid]);ALLEXCEPT('1610 Amsterdam maraton';'1610 Amsterdam maraton'[Split];'1610 Amsterdam maraton'[Løps ID]))
+CALCULATE(sum(ManuellKorr[Tid]);ALLEXCEPT(ManuellKorr;ManuellKorr[Løps ID];ManuellKorr[Split]))What I want to do is to add the times [Tid] from two different tables together. Total should be 3:09:00.
Here's the file:
- v-huizhn-msft
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