Forum Discussion
Power Query under Power BI desktop create/add column for count of grouping
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
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