Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
KBD
Helper I
Helper I

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

KBD_0-1728690678769.png

 

 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

 

8 REPLIES 8
Power_BI9
New Member

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.

KBD
Helper I
Helper I

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

shafiz_p
Super User
Super User

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.

shafiz_p_0-1728710338495.png

 

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

shafiz_p_1-1728710537717.png

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:

shafiz_p_2-1728710795972.png

 

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

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

 

KBD_1-1728853142495.png

 

 

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

 

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:  

 

KBD_0-1728847586209.png

 

 

The below is sorted by  Asset NumberyearWeekStartDate

 

KBD_1-1728847586213.png

 

 

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

 

lbendlin
Super User
Super User

lbendlin_0-1728696544980.png

 

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.

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

 

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors