Row value in column -power bi

[ABC11]

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

[lbendlin]

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"

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".

[lbendlin]

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"

[ABC11]

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,

[v-yiruan-msft · Accepted 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:

Connect to SQL Server

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]

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).

[lbendlin]

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.

[ABC11]

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,

[lbendlin]

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".

[ABC11]

ok. Thanks

[v-yiruan-msft]

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