Forum Discussion

ABC11's avatar
ABC11
Icon for Resolver I rankResolver I
4 years ago
Solved

Row value in column -power bi

Hi All,

I have source data look like this.

VENDOR_NAMELASTNAMEFIRSTNAMEPERSONIDLABORHRSSITEIDLABORLINECOST
ABD LTDADAMSFARAH3000800HOR12000
ABD LTDAHMEDDAVID3001300HOR6000
ABD LTDAHMEDDAVID3001195ALB3900
ABD LTDAKOYBOL2991400HOR10400
ABD LTDAKOYBOL2991500ALB13000
ABD LTDAWGARY5991900HOR13500

 

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_NAMELASTNAMEFIRSTNAMEPERSONIDHOR_LABORHRSALB_LABORHRSRATIOHOR_LABORLINECOSTALB_LABORLINECOST %  
ABD LTDADAMSFARAH30008000need to caculate %120000  
ABD LTDAHMEDDAVID3001300195need to caculate %60003900  
ABD LTDAKOYBOL2991400500need to caculate %1040013000  
ABD LTDAWGARY59919000 135000  
ABD LTD      

 

   
           
           
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

11 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        ABC11
        Icon for Resolver I rankResolver I
        4 years ago

        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,