Forum Discussion
Resolver IRow value in column -power bi
Hi ABC11 ,
I'm not clear about your final expected result. Do you want to create a report in Power BI? If yes, you can refer the following documentations to get it:
Refresh data from an on-premises SQL Server database
1. Connect to SQL Server database and put your SQL query into SQL statement textbox under Advanced options tab just as below screenshot...
2. Transform the data in Power Query Editor
3. Create visualizations
4. Publish report to Power BI Service
As for how to get % and ratio, you can get them by creating measures, calculated columns, etc. Before that you may need to provide the corresponding calculation logic so that we can provide you with a suitable solution later.
Percentage= DIVIDE(SUM('Table'[HOR_LABORHRS]),SUM('Table'[ALB_LABORHRS]))Best Regards
Hi ABC11 ,
I'm not clear about your final expected result. Do you want to create a report in Power BI? If yes, you can refer the following documentations to get it:
Refresh data from an on-premises SQL Server database
1. Connect to SQL Server database and put your SQL query into SQL statement textbox under Advanced options tab just as below screenshot...
2. Transform the data in Power Query Editor
3. Create visualizations
4. Publish report to Power BI Service
As for how to get % and ratio, you can get them by creating measures, calculated columns, etc. Before that you may need to provide the corresponding calculation logic so that we can provide you with a suitable solution later.
Percentage= DIVIDE(SUM('Table'[HOR_LABORHRS]),SUM('Table'[ALB_LABORHRS]))Best Regards
Resolver IHello Yingyinr,
Thanks for your time.
There are large amount of data.
We have different SITEID: currently I am doing only HORIZON and ALBIAN.
We have different paycode 801,802,814,830,809,826,840,940
AS example: 801 for regular time; 802+814+830 for time and half(1.5); 809+826+840 for double time
Below is my data comeing from Oracle server.
| Contractor# | Contractor Name | PayCode | SITEID | LaborHrs | Total Cost |
| 10000 | NOBLE, SKYLER | 801 | HORIZON | 300 | $ 4,500.00 |
| 10000 | NOBLE, SKYLER | 802 | HORIZON | 500 | $ 7,500.00 |
| 10000 | NOBLE, SKYLER | 826 | HORIZON | 900 | $ 13,500.00 |
| 10000 | NOBLE, SKYLER | 801 | ALBIAN | 800 | $ 12,000.00 |
| 10000 | NOBLE, SKYLER | 814 | ALBIAN | 600 | $ 9,000.00 |
| 10000 | NOBLE, SKYLER | 809 | ALBIAN | 400 | $ 6,000.00 |
| 300020 | MACDONALD, JAMES | 801 | HORIZON | 400 | $ 6,000.00 |
| 300020 | MACDONALD, JAMES | 801 | ALBIAN | 200 | $ 3,000.00 |
| 300020 | MACDONALD, JAMES | 802 | HORIZON | 150 | $ 2,250.00 |
| 300020 | MACDONALD, JAMES | 809 | HORIZON | 50 | $ 750.00 |
| 31002 | OBRIEN, GREG | 801 | ALBIAN | 900 | $ 13,500.00 |
| 31002 | OBRIEN, GREG | 802 | ALBIAN | 400 | $ 6,000.00 |
| 31002 | OBRIEN, GREG | 814 | ALBIAN | 200 | $ 3,000.00 |
| 300106 | NEWMAN, DALTON | 801 | HORIZON | 800 | $ 12,000.00 |
| 300106 | NEWMAN, DALTON | 801 | HORIZON | 400 | $ 3,600.00 |
| 300106 | NEWMAN, DALTON | 802 | HORIZON | 200 | $ 6,000.00 |
| 300106 | NEWMAN, DALTON | 814 | HORIZON | 50 | $ 10,800.00 |
| 300106 | NEWMAN, DALTON | 809 | HORIZON | 10 | $ 9,600.00 |
| 300106 | NEWMAN, DALTON | 826 | HORIZON | 50 | $ 7,200.00 |
| 300106 | NEWMAN, DALTON | 801 | ALBIAN | 200 | $ 4,800.00 |
| 300106 | NEWMAN, DALTON | 802 | ALBIAN | 12 | $ 4,800.00 |
| 300106 | NEWMAN, DALTON | 830 | ALBIAN | 12 | $ 2,400.00 |
| 300106 | NEWMAN, DALTON | 814 | ALBIAN | 10 | $ 1,800.00 |
| 300106 | NEWMAN, DALTON | 809 | ALBIAN | 24 | $ 600.00 |
| 300106 | NEWMAN, DALTON | 826 | ALBIAN | 24 | $ 10,800.00 |
I would like my output in below
| Employee Name | Contract #'s | Horizon Regular Hours | Horizon Time and Half | Horizon Double Time | Albian Hours | Horizon Regular Cost | Horizon Time and Half Cost | Horizon Double Time Cost | Albian Cost | Base Rate | NON LEM hours | Horizon Total LEM Hours | Outside Horizon Non LEM Hours | Total Hours(Albian+Horizon) | Horizon Percentage Owed |
So basically I would like to have: Total Regular hrs, Total time and half, Total Double time hrs, Total hrs for Horizon site. and Total hrs for Albian site. Also same thing with cost.
I would say calculation column would be prefer because data is very large.
Thanks again
- lbendlin
Super User
As I mentioned earlier the transforms required are not something you can do dynamically in Power Query. You will have to do the majority of the work in the upstream system (your Oracle database query).
