Forum Discussion

KBD's avatar
KBD
Helper III
1 year ago

Power Query under Power BI desktop create/add column for count of grouping

Hello:

 

New to the PowerQuery/ Power BI world.

I have a simple requirement..  I need to create a column which is the count of a two column grouping.

In Power BI this works:  

weeklyNumMaint = CALCULATE( DISTINCTCOUNT(  'Maint Data Extract'[Work order])  ,  ALLEXCEPT(   'Maint Data Extract', 'Maint Data Extract'[Asset number],  'Maint Data Extract'[weekYearMaint]  )   )
 
Gives me for an asset,  for a week-year the number of work orders.  Then I create categories off this number as follows:  
weeklyMaintFlag = IF('Maint Data Extract'[weeklyNumMaint] = 1 , "One Maintenance for the week",  "More than One Maintenance for the Week" )
 
All good except I believe and this may be wrong I cannot use this measure as a label on a Stacked Bar Chart.  Is this true?
 
OK so if the is true then just create the attribute in Power Query, doing this for other attributes already.
 Given the first three cols  I need to create the last two.
Asset NoWork OrderWeekYearWOs / WeekFlag
99910730019041-20201One Work Order
99910629865391-20202Multi Work Orders
99910629915741-20202Multi Work Orders
15810429915921-20201One Work Order
15810429915932-20201One Work Order
33711029819652-20201One Work Order
33711029819663-20201One Work Order
33711029819187-20201One Work Order
10750929819198-20201One Work Order
107633297957210-20202Multi Work Orders
107633297957310-20202Multi Work Orders
107509298957313-20201One Work Order
158104298957414-20201One Work Order
999106298957514-20201One Work Order

 

In Power Query I try to create a col like this

 

 Gives me cyclic error.    

Created this code:

= Table.AddColumn(ExpiryFlag, "AssetWeekly_WO", each Table.Group( #"Maint Data Extract", {"[Asset number]", "[weekYearMaint]" } , { "newCol", each Table.RowCount(_) , Int64.Type } ))

 

How ExpiryFlag got in there is beyond me!

 

Your kind assistance is requested

 

KBD

 

Need Help

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBLDoMwDATQu2RNpXGM48xZEPe/BomhlLaE3ShP8W9ZEkmBpykpIMTckrwyMtI6HVraW2Ytphwqxfznr1iVqBfK/Kja00dVXQR7X2GxR+0T6EiltuSXvnADT+2pfmnRmIVO85gZQ9Z/Pmu/WW9X7hznmu9v3diuvG4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Asset No" = _t, #"Work Order" = _t, WeekYear = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Asset No", "WeekYear"}, {{"WOs / Week", each Table.RowCount(_), Int64.Type}, {"Rows", each _, type table [Asset No=nullable text, Work Order=nullable text, WeekYear=nullable text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Work Order"}, {"Work Order"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Asset No", "Work Order", "WeekYear", "WOs / Week"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Flag", each if [#"WOs / Week"]=1 then "One Work Order" else "Multi Work Order")
in
    #"Added Custom"

    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". Once you examined the code, replace the Source step with your own source.

    • KBD's avatar
      KBD
      Helper III
      1 year ago

      Ibendlin:

       

      Thanks for your responce.  I believe I have a solution that takes me as far as the row count column.

       As follows:

      let
      Source = Excel.Workbook(File.Contents("C:\Users\ne098406\OneDrive - New York Power Authority\dev\FleetManagement\PowerBI\Data\NYPA Fleet Analytics Dashboard.twb Files\Maint Data Extract.xlsx"), null, true),
      #"Maint Data Extract_Sheet" = Source{[Item="Maint Data Extract",Kind="Sheet"]}[Data],
      #"Promoted Headers" = Table.PromoteHeaders(#"Maint Data Extract_Sheet", [PromoteAllScalars=true]),
      #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Asset number", type text}}),
      weekOfYearStart = Table.AddColumn(#"Changed Type", "weekOfYearStart", each Date.WeekOfYear([Start date] )),
      yearStart = Table.AddColumn(weekOfYearStart, "yearStart", each Date.Year( [Start date] )),
      yearWeekStart = Table.AddColumn(yearStart, "yearWeekStartDate", each Number.ToText( [yearStart], "D4" ) & "-" & Number.ToText( [weekOfYearStart], "D2" )),
      #"Added Custom" = Table.AddColumn(yearWeekStart, "Custom", each Table.Group(yearWeekStart, {"Asset number", "yearWeekStartDate"}, {{"AssetWeek_WO_Count", each Table.RowCount(_), Int64.Type}})),
      AssetWeek_WO_cnt = #"Added Custom"{0}[Custom]
      in
      AssetWeek_WO_cnt 

       

      Problem:

      How do I join Asset Number, yearWeekStartDate & AssetWeek_WO_Count back to the WorkOrder table?  It would be a join on Asset Number & yearWeekStartDate?

       

      Thanks for your kind assistance

       

      KBD

       

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        Avoid joins in Power Query if possible. Let the Power BI data model do the work for you.

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi KBD  Considering below is your data set and you want to create other 2 columns based on this information. Changing Work Order to Text type.

     

    By using grouping technique in power query, you can create WOCount Column. See Images:

    I have created 2 columns, one is counting work order for each asset and week-Year and other is concatenating multiple work orders. If you don't want this, just remove the last part of the formula.

    Now create a column to flag work orders, if 1 then One work order else multiple work orders. See image below:

     

    Now you can use this as a label in stack bar chart or other visual you like. Also, your work order counting will be correct.


    Hope this helps!!

    If the answer solved your problem, please accept it as a solution!!

     

    Best Regards,
    Shahariar Hafiz

    • KBD's avatar
      KBD
      Helper III
      1 year ago

      Thanks Shahariar for your swift reply.

       

      Some progress

      In order to simplify things, left out about twenty columns in my work order table.

      The two new columns need to be joined back to the Work Order table.

      Joined back using Asset No & WeekYear.

       

      This what I have:

      Change the type of Asset Number:

      = Table.TransformColumnTypes(#"Promoted Headers",{{"Asset number", type text}})

       

      Create the Week Year Col's:

      = Table.AddColumn(#"Changed Type", "weekOfYearStart", each Date.WeekOfYear([Start date] ))

      = Table.AddColumn(weekOfYearStart, "yearStart", each Date.Year( [Start date] ))

      = Table.AddColumn(yearStart, "yearWeekStartDate", each Number.ToText( [yearStart], "D4" ) & "-" & Number.ToText( [weekOfYearStart], "D2" ))

      Up to here is fine.

       

      Next group the work orders by Asset Number & yearWeekStartDate and get the count.

      = Table.AddColumn(yearWeekStart, "Custom", each Table.Group(yearWeekStart, {"Asset number", "yearWeekStartDate"}, {{"AssetWeek_WO_Count", each Table.RowCount(_), Int64.Type}}))

       

      This runs but the results are wrong.  It creates a Column Custom containing a Table

      Table created look like this:  

       

       

       

      The below is sorted by  Asset Number & yearWeekStartDate

       

       

       

      The assets above do not have two tickets in that week.  Assets 103608, 103820 & 103896 

      only have one ticket each in the data.

       

      Is my Table.Group above wrong?  

       

      When the grouping and count is fixed -->  how to merge/join back to the Workorder table?

       

       

      Your kind assistance is requested.

       

      KBD

       

    • KBD's avatar
      KBD
      Helper III
      1 year ago

      Thanks Shahariar for your swift reply.

       

      Shahariar, disregard prior reply.

      I confused myself beyond repair.  

       

      I followed your guidance and made progress.  Thanks!

      This creates the Asset - week-year count col.

      = Table.AddColumn(yearWeekStart, "Custom", each Table.Group(yearWeekStart, {"Asset number", "yearWeekStartDate"}, {{"AssetWeek_WO_Count", each Table.RowCount(_), Int64.Type}}))

      Create the flag

      = Table.AddColumn(Custom, "AssetWeek_WOFlag", each if [AssetWeek_WO_Count] = 1 then "One Work Order in Week" else "Multi Work Order in Week")

       

       

       

      Looks good and counts check out. 

       

      In order to simplify things, left out about twenty columns in my work order table.

      The two new columns need to be joined back to the Work Order table.

      Joined back using Asset No & WeekYear.

      How do I join this back to the original data?

       

       

      Thanks for your kind assistance

       

      KBD

       

  • KBD's avatar
    KBD
    Helper III
    1 year ago

    If I was doing this in SQL it would be something like this:

     

    with ay_count as
       (select assetNo, yearweek, count() as assetWeek_WO_count
        from workorder
       group by assetno, yearweek  )

     

    select wo.assetNo, wo.yearweek, wo.col1, wo.col2, wo.col3,.... assetWeek_WO_count
    * from workorder wo, ay_count
    where wo.assetNo = ay_count.assetNo and
    wo.yearweek = ay_count.yearweek

     

    As per my last reply to Shahariar, I believe I have the first select working in Power Query.

    How is the second Select statement above done?

     

    Thanks for your kind assistance.

     

    KD

  • Power_BI9's avatar
    Power_BI9
    New Member
    1 year ago

    To fix the cyclic error and achieve your goal in Power Query:

    1. Group by Asset No and WeekYear:

      • Use Group By in Power Query to group by Asset No and WeekYear, counting Work Orders per group.

    2. Add Conditional Column:

      • Add a conditional column for the flag:
        • If WOs / Week = 1, then "One Work Order."
        • Else "Multi Work Orders."

    Power Query Code:

     

    let
    Source = YourTableNameHere,
    GroupedData = Table.Group(Source, {"Asset No", "WeekYear"}, {{"WOs / Week", each Table.RowCount(_), Int64.Type}}),
    AddFlagColumn = Table.AddColumn(GroupedData, "Flag", each if [WOs / Week] = 1 then "One Work Order" else "Multi Work Orders")
    in
    AddFlagColumn

     

    This resolves the issue and adds the desired columns.