Forum Discussion
Help Building Deadlines for Single Items with Multiple IDs
Hi quincy_p,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.
Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!
Hi quincy_p,
Thank you Jaywant-Thorat and amitchandak for your response to the query.
The reason your Cycles column is returning null is because the Contract_PM_Frequency field is missing from the grouped table after the Group By step. In Power Query, columns not aggregated during grouping are removed, so the frequency value only remains inside the AllRows sub-table. As a result, the Cycles step cannot access it and returns null for each row. To resolve this, extract the frequency during the Group By step by adding an aggregated column, such as PM_Frequency = each List.Max([Contract_PM_Frequency]), in Table.Group. This will make the PM_Frequency field available at the grouped level, allowing your Cycles calculation to reference it. With Start, End, and Frequency in the same row, the List.Generate function can create the correct list of PM deadlines, and expanding the Cycles list will yield one row per deadline as intended.
Thank you.
Sorry for the delay - I am not sure what you mean (do not know power query well enough) - below is my group by.
Are you suggesting to add into this query or to add it as an additonal step before/after?
= Table.Group(
#"Renamed Columns",
{"Contract Number"},
{
{"Original_Start_Date", each List.Min([SVMXC__Start_Date__c]), type nullable date},
{"Final_End_Date", each List.Max([SVMXC__End_Date__c]), type nullable date},
{"AllRows", each _, type table [
Id=nullable text,
OwnerId=nullable text,
Contract Number=nullable text,
CreatedDate=nullable datetime,
SVMXC__Start_Date__c=nullable date,
SVMXC__End_Date__c=nullable date,
SVMXC__Company__c=nullable text,
SVMXC__EndpointURL__c=nullable text,
Contract_Status__c=nullable text,
External_ID__c=nullable text,
SVMX_Expiration_Status__c=nullable text,
Country_OU__c=nullable text,
ERP_Account_Number__c=nullable text,
EU_SLA_Terms__c=nullable text,
Medical_Medsurg__c=nullable text,
Qualified_Covered_Product_Count__c=nullable number,
Total_Active_Covered_Products__c=nullable number
]}
}
)
Hi quincy_p,
In Power Query, using Group By will remove any columns that are not aggregated from the grouped output. As a result, your Contract_PM_Frequency value was only present within the AllRows table, making it inaccessible to the Add Cycles step. To resolve this, you should include the frequency field in the Table.Group aggregation, such as:
{"PM_Frequency", each List.Max([Contract_PM_Frequency]), type number}
After adding this field, the grouped row now includes all necessary values (Start Date, End Date, and Frequency), which enables the Cycles list to generate properly with List.Generate(). Here is the corrected structure: group by Contract Number and include Start Date, End Date, and PM_Frequency; use the new [PM_Frequency] column in the Add Cycles step; and expand the Cycles list to create one row per deadline.
This update will ensure that deadlines are calculated correctly and will prevent any null values.
Table.Group - PowerQuery M | Microsoft Learn
List.Generate - PowerQuery M | Microsoft Learn
Type functions - PowerQuery M | Microsoft LearnThank you.
Hi quincy_p,
I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.
Thank you.