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.
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