Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
Asset No | Work Order | WeekYear | WOs / Week | Flag |
999107 | 3001904 | 1-2020 | 1 | One Work Order |
999106 | 2986539 | 1-2020 | 2 | Multi Work Orders |
999106 | 2991574 | 1-2020 | 2 | Multi Work Orders |
158104 | 2991592 | 1-2020 | 1 | One Work Order |
158104 | 2991593 | 2-2020 | 1 | One Work Order |
337110 | 2981965 | 2-2020 | 1 | One Work Order |
337110 | 2981966 | 3-2020 | 1 | One Work Order |
337110 | 2981918 | 7-2020 | 1 | One Work Order |
107509 | 2981919 | 8-2020 | 1 | One Work Order |
107633 | 2979572 | 10-2020 | 2 | Multi Work Orders |
107633 | 2979573 | 10-2020 | 2 | Multi Work Orders |
107509 | 2989573 | 13-2020 | 1 | One Work Order |
158104 | 2989574 | 14-2020 | 1 | One Work Order |
999106 | 2989575 | 14-2020 | 1 | One Work Order |
In Power Query I try to create a col like this
Gives me cyclic error.
Created this code:
= Table.AddColumn(ExpiryFlag, "AssetWeekly_WO", each Table.Group( #"Maint Data Extract", {"[Asset number]", "[weekYearMaint]" } , { "newCol", each Table.RowCount(_) , Int64.Type } ))
How ExpiryFlag got in there is beyond me!
Your kind assistance is requested
KBD
To fix the cyclic error and achieve your goal in Power Query:
Group by Asset No and WeekYear:
Add Conditional Column:
Power Query Code:
let
Source = YourTableNameHere,
GroupedData = Table.Group(Source, {"Asset No", "WeekYear"}, {{"WOs / Week", each Table.RowCount(_), Int64.Type}}),
AddFlagColumn = Table.AddColumn(GroupedData, "Flag", each if [WOs / Week] = 1 then "One Work Order" else "Multi Work Orders")
in
AddFlagColumn
This resolves the issue and adds the desired columns.
If I was doing this in SQL it would be something like this:
with ay_count as
(select assetNo, yearweek, count() as assetWeek_WO_count
from workorder
group by assetno, yearweek )
select wo.assetNo, wo.yearweek, wo.col1, wo.col2, wo.col3,.... assetWeek_WO_count
* from workorder wo, ay_count
where wo.assetNo = ay_count.assetNo and
wo.yearweek = ay_count.yearweek
As per my last reply to Shahariar, I believe I have the first select working in Power Query.
How is the second Select statement above done?
Thanks for your kind assistance.
KD
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
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
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.