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