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
Resolver IHello Ibendlin,
Thanks for your time.
Below is my original SQL query
select lf.SITEID,lv.vendor_name, EMP.lastname, EMP.FIRSTNAME, EMP.personid, lf.laborhrs,lf.workdate,lf.contractnum,lf.linetype,lf.status,lf.laborlinecost
from bi_hz_etl.lem_facts lf
join bi_hz_etl.lem_vendor lv on (lf.vendor=lv.vendor_id)
LEFT JOIN BI_HZ_ETL.LEM_EMP EMP ON (LF.laborcode = EMP.personid)
where lf.siteid in('HORIZON','ALBIAN') and lf.linetype='L'
When I copy and past your query after this query - then I got error
DataSource.Error: Oracle: ORA-00972: identifier is too long
Details:
DataSourceKind=Oracle
DataSourcePath=rptprd01
Message=ORA-00972: identifier is too long
ErrorCode=-2147467259
What would be my next step. please, guide me.
(I may need to add few more column from my source table in future.)
Thanks,
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
- ABC11
Hello 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).
