Power Query under Power BI desktop create/add column for count of grouping

Question

Hello:&nbsp;New to the PowerQuery/ Power BI world.I have a simple requirement..&nbsp; I need to create a column which is the count of a two column grouping.In Power BI this works:&nbsp;&nbsp;weeklyNumMaint = CALCULATE( DISTINCTCOUNT( &nbsp;'Maint Data Extract'[Work order]) &nbsp;, &nbsp;ALLEXCEPT( &nbsp; 'Maint Data Extract', 'Maint Data Extract'[Asset number], &nbsp;'Maint Data Extract'[weekYearMaint] &nbsp;) &nbsp; )&nbsp;Gives me for an asset,&nbsp; for a week-year the number of work orders.&nbsp; Then I create categories off this number as follows:&nbsp;&nbsp;weeklyMaintFlag = IF('Maint Data Extract'[weeklyNumMaint] = 1 , "One Maintenance for the week", &nbsp;"More than One Maintenance for the Week" )&nbsp;All good except I believe and this may be wrong I cannot use this measure as a label on a Stacked Bar Chart.&nbsp; Is this true?&nbsp;OK so if the is true then just create the attribute in Power Query, doing this for other attributes already.&nbsp;Given the first three cols&nbsp; I need to create the last two.Asset NoWork OrderWeekYearWOs / WeekFlag99910730019041-20201One Work Order99910629865391-20202Multi Work Orders99910629915741-20202Multi Work Orders15810429915921-20201One Work Order15810429915932-20201One Work Order33711029819652-20201One Work Order33711029819663-20201One Work Order33711029819187-20201One Work Order10750929819198-20201One Work Order107633297957210-20202Multi Work Orders107633297957310-20202Multi Work Orders107509298957313-20201One Work Order158104298957414-20201One Work Order999106298957514-20201One Work Order&nbsp;In Power Query I try to create a col like this&nbsp;&nbsp;Gives me cyclic error.&nbsp; &nbsp;&nbsp;Created this code:= Table.AddColumn(ExpiryFlag, "AssetWeekly_WO", each Table.Group( #"Maint Data Extract", {"[Asset number]", "[weekYearMaint]" } , { "newCol", each Table.RowCount(_) , Int64.Type } ))&nbsp;How ExpiryFlag got in there is beyond me!&nbsp;Your kind assistance is requested&nbsp;KBD&nbsp;

lbendlin · Answer

&nbsp;
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.

shafiz_p · Answer

Hi&nbsp;KBD&nbsp; Considering below is your data set and you want to create other 2 columns based on this information. Changing Work Order to Text type.&nbsp;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:&nbsp;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!!&nbsp;Best Regards,Shahariar Hafiz

kbd · Answer

Thanks&nbsp;Shahariar for your swift reply.&nbsp;Some progressIn 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&nbsp;table.Joined back using Asset No &amp; WeekYear.&nbsp;This what I have:Change the type of Asset Number:= Table.TransformColumnTypes(#"Promoted Headers",{{"Asset number", type text}})&nbsp;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" ) &amp; "-" &amp; Number.ToText( [weekOfYearStart], "D2" ))Up to here is fine.&nbsp;Next group the work orders by Asset Number &amp; yearWeekStartDate and get the count.= Table.AddColumn(yearWeekStart, "Custom", each Table.Group(yearWeekStart, {"Asset number", "yearWeekStartDate"}, {{"AssetWeek_WO_Count", each Table.RowCount(_), Int64.Type}}))&nbsp;This runs but the results are wrong.&nbsp; It creates a Column Custom containing a TableTable created look like this:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The below is sorted by &nbsp;Asset Number &amp;&nbsp;yearWeekStartDate&nbsp;&nbsp;&nbsp;The assets above do not have two tickets in that week.&nbsp; Assets 103608, 103820 &amp; 103896&nbsp;only have one ticket each in the data.&nbsp;Is my Table.Group above wrong?&nbsp;&nbsp;&nbsp;When the grouping and count is fixed --&gt;&nbsp; how to merge/join back to the Workorder table?&nbsp;&nbsp;Your kind assistance is requested.&nbsp;KBD&nbsp;

kbd · Answer

Thanks&nbsp;Shahariar for your swift reply.&nbsp;Shahariar, disregard prior reply.I confused myself beyond repair.&nbsp;&nbsp;&nbsp;I followed your guidance and made progress.&nbsp; 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")&nbsp;&nbsp;&nbsp;Looks good and counts check out.&nbsp;&nbsp;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&nbsp;Work Order&nbsp;table.Joined back using Asset No &amp; WeekYear.How do I join this back to the original data?&nbsp;&nbsp;Thanks for your kind assistance&nbsp;KBD&nbsp;

kbd · Answer

Ibendlin:&nbsp;Thanks for your responce.&nbsp; I believe I have a solution that takes me as far as the row count column.&nbsp;As follows:letSource = Excel.Workbook(File.Contents("C:\Users
e098406\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" ) &amp; "-" &amp; 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]inAssetWeek_WO_cnt&nbsp;&nbsp;Problem:How do I join Asset Number, yearWeekStartDate &amp; AssetWeek_WO_Count back to the WorkOrder table?&nbsp; It would be a join on Asset Number &amp; yearWeekStartDate?&nbsp;Thanks for your kind assistance&nbsp;KBD&nbsp;

Forum Discussion

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

User Group Leader Get Started Guide

FAQ on the Fabric User Groups

Groups