Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I have source data look like this.
VENDOR_NAME | LASTNAME | FIRSTNAME | PERSONID | LABORHRS | SITEID | LABORLINECOST |
ABD LTD | ADAMS | FARAH | 3000 | 800 | HOR | 12000 |
ABD LTD | AHMED | DAVID | 3001 | 300 | HOR | 6000 |
ABD LTD | AHMED | DAVID | 3001 | 195 | ALB | 3900 |
ABD LTD | AKOY | BOL | 2991 | 400 | HOR | 10400 |
ABD LTD | AKOY | BOL | 2991 | 500 | ALB | 13000 |
ABD LTD | AW | GARY | 5991 | 900 | HOR | 13500 |
I need to arrange to this layout in order to calculate more such as %, ratio,....
Basically - I would like to see laborhrs in two different column base on siteid.
VENDOR_NAME | LASTNAME | FIRSTNAME | PERSONID | HOR_LABORHRS | ALB_LABORHRS | RATIO | HOR_LABORLINECOST | ALB_LABORLINECOST | % | |
ABD LTD | ADAMS | FARAH | 3000 | 800 | 0 | need to caculate % | 12000 | 0 | ||
ABD LTD | AHMED | DAVID | 3001 | 300 | 195 | need to caculate % | 6000 | 3900 | ||
ABD LTD | AKOY | BOL | 2991 | 400 | 500 | need to caculate % | 10400 | 13000 | ||
ABD LTD | AW | GARY | 5991 | 900 | 0 | 13500 | 0 | |||
ABD LTD |
| |||||||||
Solved! Go to Solution.
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
Here is a very crude static-y way of doing this. Do you need this dynamic for more than the two sample locations?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7BCoMwDIZfZfTsobVWzDEl2yqrFOrYGNL3f40lFkTZDl7+DxK+5F8WhZ4u8UmqUUg4zcwbZgxMq7VmDGuGlDlNK7PSHLQwXYWEr5GqZio2rT9tGXCyjF5G8Gs90ofhU+RsAUTp9v10d8pxq1PfGPun3ZvjjllEVxXYv7FyoJQv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VENDOR_NAME = _t, LASTNAME = _t, FIRSTNAME = _t, PERSONID = _t, LABORHRS = _t, SITEID = _t, LABORLINECOST = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"VENDOR_NAME", type text}, {"LASTNAME", type text}, {"FIRSTNAME", type text}, {"PERSONID", Int64.Type}, {"LABORHRS", Int64.Type}, {"SITEID", type text}, {"LABORLINECOST", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #table({[SITEID] & "LABORHRS", [SITEID] & "LABORLINECOST"},{{[LABORHRS],[LABORLINECOST]}})),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"HORLABORHRS", "HORLABORLINECOST", "ALBLABORHRS", "ALBLABORLINECOST"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"VENDOR_NAME", "LASTNAME", "FIRSTNAME", "PERSONID", "HORLABORHRS", "HORLABORLINECOST", "ALBLABORHRS", "ALBLABORLINECOST"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"VENDOR_NAME", "LASTNAME", "FIRSTNAME", "PERSONID"}, {{"HORLABORHRS", each List.Sum([HORLABORHRS]), type nullable number}, {"HORLABORLINECOST", each List.Sum([HORLABORLINECOST]), type nullable number}, {"ALBLABORHRS", each List.Sum([ALBLABORHRS]), type nullable number}, {"ALBLABORLINECOST", each List.Sum([ALBLABORLINECOST]), type nullable number}})
in
#"Grouped Rows"
Here is a slightly more dynamic version but it still stumbles at the grouping step:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7BCoMwDIZfZfTsobVWzDEl2yqrFOrYGNL3f40lFkTZDl7+DxK+5F8WhZ4u8UmqUUg4zcwbZgxMq7VmDGuGlDlNK7PSHLQwXYWEr5GqZio2rT9tGXCyjF5G8Gs90ofhU+RsAUTp9v10d8pxq1PfGPun3ZvjjllEVxXYv7FyoJQv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VENDOR_NAME = _t, LASTNAME = _t, FIRSTNAME = _t, PERSONID = _t, LABORHRS = _t, SITEID = _t, LABORLINECOST = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"VENDOR_NAME", type text}, {"LASTNAME", type text}, {"FIRSTNAME", type text}, {"PERSONID", Int64.Type}, {"LABORHRS", Int64.Type}, {"SITEID", type text}, {"LABORLINECOST", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #table({[SITEID] & "LABORHRS", [SITEID] & "LABORLINECOST"},{{[LABORHRS],[LABORLINECOST]}})),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.ColumnNames([Custom])),
CN = List.Distinct(List.Combine(#"Added Custom1"[Custom.1])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", CN),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"VENDOR_NAME", "LASTNAME", "FIRSTNAME", "PERSONID"} & CN),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"VENDOR_NAME", "LASTNAME", "FIRSTNAME", "PERSONID"}, {{"HORLABORHRS", each List.Sum([HORLABORHRS]), type nullable number}, {"HORLABORLINECOST", each List.Sum([HORLABORLINECOST]), type nullable number}, {"ALBLABORHRS", each List.Sum([ALBLABORHRS]), type nullable number}, {"ALBLABORLINECOST", each List.Sum([ALBLABORLINECOST]), type nullable number}})
in
#"Grouped Rows"
Hello 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
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
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).
This here is a Power BI support forum, not an Oracle SQL support forum. My provided code is M, not SQL.
Since you can run custom SQL queries it would be much easier to do your required transforms directly in SQL.
Hello Ibendlin,
Yes, I understand this forum is for power bi support.
Sorry for misread Advanced editor vs Advanced query.
When I open advanced editor my SQL query is there. Still Can I do copy and past.?
Thanks,
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
ok. Thanks
Hi @ABC11 ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.
Otherwise, please provide the requried info in my previous post. We will provide you a suitable solution later.
Best Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |