Forum Discussion
quincy_p
8 months agoAdvocate I
Help Building Deadlines for Single Items with Multiple IDs
Hi everyone - long question here - I am trying to build KPIs for my team but running into some issues with the way our data is managed. Long story short - I need to measure our performance servi...
- 8 months ago
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!
quincy_p
8 months agoAdvocate I
I added that the my Contracts Table but all of the Cycles values return as Null?
let
Source = PowerPlatform.Dataflows(null),
Workspaces = Source{[Id="Workspaces"]}[Data],
#"169eb939-15ea-4fd2-818c-13d66178b545" = Workspaces{[workspaceId="169eb939-15ea-4fd2-818c-13d66178b545"]}[Data],
#"75b17dd5-cafc-488c-a448-48508445c89f" = #"169eb939-15ea-4fd2-818c-13d66178b545"{[dataflowId="75b17dd5-cafc-488c-a448-48508445c89f"]}[Data],
#"Service/Maintenance Contract_" = #"75b17dd5-cafc-488c-a448-48508445c89f"{[entity="Service/Maintenance Contract",version=""]}[Data],
// Rename Name -> Contract Number
#"Renamed Columns" =
Table.RenameColumns(
#"Service/Maintenance Contract_",
{{"Name", "Contract Number"}}
),
// Group by Contract Number
#"Grouped Rows" =
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
]}
}
),
// Select rows where SVMXC__End_Date__c = Final_End_Date
#"MaxRow Added" =
Table.AddColumn(
#"Grouped Rows",
"RowWithMaxEnd",
each Table.SelectRows(
[AllRows],
(r) => r[SVMXC__End_Date__c] = [Final_End_Date]
),
type table
),
// Expand Id and Contract_Status__c from max-end row
#"Expanded MaxRow" =
Table.ExpandTableColumn(
#"MaxRow Added",
"RowWithMaxEnd",
{"Id", "Contract_Status__c"},
{"Latest_Id", "Latest_Contract_Status"}
),
// Add Cycle list column (list of deadlines)
#"Add Cycles" =
Table.AddColumn(
#"Expanded MaxRow",
"Cycles",
each
let
Start = [Original_Start_Date],
End = [Final_End_Date],
// You may need to replace Contract_PM_Frequency with the real field name
Freq = try Number.From([Contract_PM_Frequency]) otherwise null,
Deadlines =
if Start <> null and End <> null and Freq <> null then
List.Generate(
() => Start,
each _ <= End,
each Date.AddMonths(_, Freq)
)
else
null
in Deadlines
),
// Expand Cycles into rows
#"Expand Cycles" =
Table.ExpandListColumn(#"Add Cycles", "Cycles")
in
#"Expand Cycles"