custom field with function that references a date table displays blank in service but not in desktop

[bdunzweiler]

Hi Community,

I’m having difficulties with a custom field that works in the desktop, but is blank in the app service.  The field in question invokes a custom function that returns a date from a dates table.

This is the function:

/*GetBillMonthStart*/
(revenueMonth) =>
                Date.From(Table.SingleRow(Table.SelectRows(ForecastBillingMonths, each Date.From([StartDate]) <= Date.From(revenueMonth) and Date.From([EndDate]) >= Date.From(revenueMonth)))[StartDate])

Data from ForecastBillingMonths

Line in question:

#"Added BillMonthStart" = Table.AddColumn(Result, "BillMonthStart", each GetBillMonthStart([Revenue Month])),

Complete query:

(This code is taking a single CRM opportunity record and creating a record for each month that is visible in the visualization [3 month pivot] and splitting forecasted revenue of the total opp in the appropriate months in case you are wondering)

let
    StartDate = Date.From(Table.Min(ForecastBillingMonths, "StartDate")[StartDate]),
    EndDate = Date.From(Table.Max(ForecastBillingMonths, "EndDate")[EndDate]),
    Source = OData.Feed("https://abc.api.crm.dynamics.com/api/data/v8.2/opportunities?$select=name,_ownerid_value,_parentaccountid_value,abc_estprojectstartdate,abc_solutionarchitect,abc_technicalarchitect, estimatedvalue,new_monthlyrunrate,closeprobability,abc_weightedpipeline,estimatedclosedate,abc_projectduration&$filter=_owningbusinessunit_value eq " & BusinessUnit_Solutions & " and statecode eq " & OpportunityStateCode_Open),
    #"Filtered Rows" = Table.SelectRows(Source, each Date.From([abc_estprojectstartdate]) >= StartDate and Date.From([abc_estprojectstartdate]) <= EndDate)
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows ",{{"name", "Opp Name"}, {"abc_solutionarchitect", "Solution Architect"}, {"abc_technicalarchitect", "Technical Architect"}, {"estimatedvalue", "Est. Amount"}, {"new_monthlyrunrate", "Monthly Run Rate"}, {"closeprobability", "Probability"}, {"abc_weightedpipeline", "Weighted Pipeline"}, {"estimatedclosedate", "Est. Close Date"}, {"abc_projectduration", "Project Duration"}, {"abc_estprojectstartdate", "Project Start Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Solution Architect", type text}, {"Technical Architect", type text}, {"Est. Amount", Int64.Type}, {"Monthly Run Rate", Int64.Type}, {"Probability", Int64.Type}, {"Weighted Pipeline", type number}, {"Project Start Date", type datetime}, {"Est. Close Date", type datetime}, {"Project Duration", Int64.Type}}),
    #"Added Est. Project End" = Table.AddColumn(#"Changed Type", "Est. Project End", each Date.AddMonths([Project Start Date], [Project Duration]), type date),
    #"Added Revenue Month" = Table.AddColumn(#"Added Est. Project End", "Revenue Month", each GetBillMonthStart([Project Start Date]), type date),
    Records = Table.ToRecords(#"Added Revenue Month"),
    NumOfMonths = Table.RowCount(ForecastBillingMonths),
    Expand = (x) => List.Generate(
/* starting point */
        () => Record.Combine({x, [Revenue Month=EndOfBillMonth(Date.AddDays(Date.From(DateTime.LocalNow()), (-1 * ForecastDaysInPast))), Counter=0]}),
/* end expression */
        each [Counter] < NumOfMonths, 
/* how to make next record */
        each [Counter=[Counter]+1],
/* what to put in list */
        each Record.Combine({x, [
            Revenue Month=ForecastBillingMonths{Counter}[EndDate], 
            Counter=[Counter], 
            Network Days in Project=GetNetworkDays (x[Project Start Date], x[Est. Project End])
        ]})),
    Transformed = List.Transform(Records, each Expand(_)),
    Combined = List.Combine(Transformed),
    Result = Table.FromRecords(Combined),
    #"Added BillMonthStart" = Table.AddColumn(Result, "BillMonthStart", each GetBillMonthStart([Revenue Month])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added BillMonthStart",{{"Network Days in Project", Int64.Type}}),
    #"Added Days Occuring in Month" = Table.AddColumn(#"Changed Type1", "Days Occurring in Month", each GetDaysOccuringInMonth([Project Start Date], [Est. Project End], [Revenue Month])),
    #"Added Probability2" = Table.AddColumn(#"Added Days Occuring in Month", "Probability2", each [Probability]/100, Percentage.Type),
    #"Removed Old Probability" = Table.RemoveColumns(#"Added Probability2",{"Probability"}),
    #"Renamed Probability 2" = Table.RenameColumns(#"Removed Old Probability",{{"Probability2", "Probability"}}),
    #"Added Weighted Monthly Revenue" = Table.AddColumn(#"Renamed Probability 2", "Weighted Monthly Revenue", each [Est. Amount] / [Network Days in Project] * [Probability] * [Days Occurring in Month]),
    #"Set Proper Types" = Table.TransformColumnTypes(#"Added Weighted Monthly Revenue",{{"Est. Amount", Currency.Type}, {"Monthly Run Rate", Currency.Type}, {"Weighted Pipeline", Currency.Type}, {"Est. Project End", type date}, {"Days Occurring in Month", Int64.Type}, {"Revenue Month", type date}, {"Weighted Monthly Revenue", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Set Proper Types",{"Counter"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"_parentaccountid_value", "_ownerid_value", "Solution Architect", "Monthly Run Rate", "Technical Architect", "Est. Amount", "Weighted Pipeline", "Opp Name", "owninguser", "ownerid", "parentaccountid", "Est. Close Date", "Project Duration", "Project Start Date", "Est. Project End", "Revenue Month", "Weighted Monthly Revenue", "Network Days in Project", "Days Occurring in Month", "Probability"})
in
    #"Reordered Columns"

I originally had the functions nested within the query, but then tried moving them out to see if it had any effect.

Any help would be greatly appreciated!

-Brian

[bdunzweiler · Accepted solution]

So, I figured out the issue after many one-change publishes to isolate the issue.  It appears the issue was that the current query needed local variables aka references to the other queries in order to use them as filters.  Otherwise, the referenced query was empty.

Before

/*GetBillMonthStart*/
(revenueMonth) =>
                Date.From(Table.SingleRow(Table.SelectRows(ForecastBillingMonths, each Date.From([StartDate]) <= Date.From(revenueMonth) and Date.From([EndDate]) >= Date.From(revenueMonth)))[StartDate])

After

/*GetBillMonthStart*/
(forecastBillingMonthsTable, revenueMonth) =>
                Date.From(Table.SingleRow(Table.SelectRows(forecastBillingMonthsTable, each Date.From([StartDate]) <= Date.From(revenueMonth) and Date.From([EndDate]) >= Date.From(revenueMonth)))[StartDate])

The full query with updated references

let
    BillingMonths = ForecastBillingMonths, /* NEW */
    StartDate = Date.From(Table.Min(BillingMonths, "StartDate")[StartDate]), /* REFACTORED TO USE VARIABLE */
    EndDate = Date.From(Table.Max(BillingMonths, "EndDate")[EndDate]), /* REFACTORED TO USE VARIABLE */
    DateTable = Dates, /* NEW */
    Source = OData.Feed("https://abc.api.crm.dynamics.com/api/data/v8.2/opportunities?$select=name,_ownerid_value,_parentaccountid_value,abc_estprojectstartdate,abc_solutionarchitect,abc_technicalarchitect, estimatedvalue,new_monthlyrunrate,closeprobability,abc_weightedpipeline,estimatedclosedate,abc_projectduration&$filter=_owningbusinessunit_value eq " & BusinessUnit_Solutions & " and statecode eq " & OpportunityStateCode_Open),
    #"Filtered Rows" = Table.SelectRows(Source, each Date.From([abc_estprojectstartdate]) >= StartDate and Date.From([abc_estprojectstartdate]) <= EndDate)
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows ",{{"name", "Opp Name"}, {"abc_solutionarchitect", "Solution Architect"}, {"abc_technicalarchitect", "Technical Architect"}, {"estimatedvalue", "Est. Amount"}, {"new_monthlyrunrate", "Monthly Run Rate"}, {"closeprobability", "Probability"}, {"abc_weightedpipeline", "Weighted Pipeline"}, {"estimatedclosedate", "Est. Close Date"}, {"abc_projectduration", "Project Duration"}, {"abc_estprojectstartdate", "Project Start Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Solution Architect", type text}, {"Technical Architect", type text}, {"Est. Amount", Int64.Type}, {"Monthly Run Rate", Int64.Type}, {"Probability", Int64.Type}, {"Weighted Pipeline", type number}, {"Project Start Date", type datetime}, {"Est. Close Date", type datetime}, {"Project Duration", Int64.Type}}),
    #"Added Est. Project End" = Table.AddColumn(#"Changed Type", "Est. Project End", each Date.AddMonths([Project Start Date], [Project Duration]), type date),
    #"Added Revenue Month" = Table.AddColumn(#"Added Est. Project End", "Revenue Month", each GetBillMonthStart(BillingMonths, [Project Start Date]), type date), /* PASSING IN REFERENCE */
    Records = Table.ToRecords(#"Added Revenue Month"),
    NumOfMonths = Table.RowCount(BillingMonths),
    Expand = (x) => List.Generate(
/* starting point */
        () => Record.Combine({x, [Counter=0]}),
/* end expression */
        each [Counter] < NumOfMonths, 
/* how to make next record */
        each [Counter=[Counter]+1],
/* what to put in list */
        each Record.Combine({x, [
            Revenue Month=ForecastBillingMonths{Counter}[EndDate], 
            Counter=[Counter], 
            Network Days in Project=GetNetworkDays (DateTable, x[Project Start Date], x[Est. Project End])
        ]})),
    Transformed = List.Transform(Records, each Expand(_)),
    Combined = List.Combine(Transformed),
    Result = Table.FromRecords(Combined),
    #"Added BillMonthStart" = Table.AddColumn(Result, "BillMonthStart", each GetBillMonthStart(BillingMonths, [Revenue Month])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added BillMonthStart",{{"Network Days in Project", Int64.Type}}),
    #"Added Days Occuring in Month" = Table.AddColumn(#"Changed Type1", "Days Occurring in Month", each GetDaysOccuringInMonth(DateTable, BillingMonth, [Project Start Date], [Est. Project End], [Revenue Month])),
    #"Added Probability2" = Table.AddColumn(#"Added Days Occuring in Month", "Probability2", each [Probability]/100, Percentage.Type),
    #"Removed Old Probability" = Table.RemoveColumns(#"Added Probability2",{"Probability"}),
    #"Renamed Probability 2" = Table.RenameColumns(#"Removed Old Probability",{{"Probability2", "Probability"}}),
    #"Added Weighted Monthly Revenue" = Table.AddColumn(#"Renamed Probability 2", "Weighted Monthly Revenue", each [Est. Amount] / [Network Days in Project] * [Probability] * [Days Occurring in Month]),
    #"Set Proper Types" = Table.TransformColumnTypes(#"Added Weighted Monthly Revenue",{{"Est. Amount", Currency.Type}, {"Monthly Run Rate", Currency.Type}, {"Weighted Pipeline", Currency.Type}, {"Est. Project End", type date}, {"Days Occurring in Month", Int64.Type}, {"Revenue Month", type date}, {"Weighted Monthly Revenue", type number}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Set Proper Types",{"Counter"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"_parentaccountid_value", "_ownerid_value", "Solution Architect", "Monthly Run Rate", "Technical Architect", "Est. Amount", "Weighted Pipeline", "Opp Name", "owninguser", "ownerid", "parentaccountid", "Est. Close Date", "Project Duration", "Project Start Date", "Est. Project End", "Revenue Month", "Weighted Monthly Revenue", "Network Days in Project", "Days Occurring in Month", "Probability"})
in
    #"Reordered Columns"

If there is a better way to do this, please chime in.  Otherwise, hope this helps someone else out.

-Brian