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 = ...
Need Help
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.