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!
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 Learn
Thank 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.