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
Super UserHere 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"
ABC11Hello 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=-2147467259What would be my next step. please, guide me.
(I may need to add few more column from my source table in future.)
Thanks,
- 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.