Solved: Unsolveable Mashup Data Access Error

regnidem16 · ‎07-25-2025

Hello,

I am having a data mashup error when I try to refresh my data in the Power BI workspace. This is something that just started happening randomly about a month ago. There were no changes made and then the autorefresh stopped working. I can't even do a manual refresh in the Power BI service. The full error is below

Spoiler

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[Unable to combine data] Section1/Revenue Segment - Load/2nd_Page_Table_Updated references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://api.monday.com/v2\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacyError"}}],"exceptionCulprit":1}}} Table: Detailed Report.
Cluster URI: WABI-US-NORTH-CENTRAL-G-PRIMARY-redirect.analysis.windows.net
Activity ID: 05587b44-3471-4151-889b-aa89d98118c0
Request ID: 6b1245ea-8214-cea4-88f3-81c24b36a6c9
Time: 2025-07-21 19:04:17Z

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[Unable to combine data] Section1/Revenue Segment - Load/2nd_Page_Table_Updated references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://api.monday.com/v2\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacyError"}}],"exceptionCulprit":1}}} Table: Detailed Report.Cluster URI: WABI-US-NORTH-CENTRAL-G-PRIMARY-redirect.analysis.windows.netActivity ID: 05587b44-3471-4151-889b-aa89d98118c0Request ID: 6b1245ea-8214-cea4-88f3-81c24b36a6c9Time: 2025-07-21 19:04:17Z

I know that this is usually a security level issue. However, I've already

1) Change settings in Power BI desktop to ignore security levels

2) In the Power BI service, I've set all data sources to the same security level (Organizational).

3) I know this can be caused by merging tables. However, Revenue Segment - Load is not the table that is being merged. The table that is being merged in Revenue Segment - Staging. I am even using Table.Buffer() to try and solve this issue. There are other tables being imported the same way and that get merged with Detailed Report earlier (User Resource and Dept - Staging) and there are no issues merging those.

Below are my queries in M

Detailed Report

Spoiler

let
Source = Table.Buffer(#"Detailed Report - Staging"),
#"Expanded Custom Fields" = Table.ExpandListColumn(Source, "Custom Fields"),
#"Expanded Custom Fields1" = Table.ExpandRecordColumn(#"Expanded Custom Fields", "Custom Fields", {"customFieldId", "value"}, {"Custom Fields.customFieldId", "Custom Fields.value"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Custom Fields1",null,"test",Replacer.ReplaceValue,{"Custom Fields.customFieldId"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"Custom Fields.customFieldId"]), "Custom Fields.customFieldId", "Custom Fields.value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"614b79ce8fc8f836af593b78", "614dce95b49ce00a90d4c54b", "test"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"614b3a5a41f10a506b8d1fdf", "Clockify Custom Project Number"}, {"60aff4741a4155464943ab9b", "Clockify Custom Revenue Segment"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns2", #"ORS Projects - Dummy Rows"}),
#"Sorted Rows1" = Table.Sort(#"Appended Query",{{"Date", Order.Descending}}),
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows1", {"User Name"}, Table.Buffer(#"User Resource Title and Dept - staging"), {"Column1.items_page.items.name"}, "User Resource Title and Dept - staging", JoinKind.LeftOuter),
#"Expanded User Resource Title and Dept - staging" = Table.ExpandTableColumn(#"Merged Queries", "User Resource Title and Dept - staging", {"Resource Title", "Resource Dept.", "Employment Status"}, {"User Resource Title and Dept - staging.Resource Title", "User Resource Title and Dept - staging.Resource Dept.", "User Resource Title and Dept - staging.Employment Status"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded User Resource Title and Dept - staging", {"Project Name"}, Table.Buffer(#"Revenue Segment - Staging"), {"Name"}, "Revenue Segment - Staging", JoinKind.LeftOuter),
#"Expanded Revenue Segment - Staging" = Table.ExpandTableColumn(#"Merged Queries1", "Revenue Segment - Staging", {"Revenue Segment", "Billable Work?"}, {"Revenue Segment - Staging.Revenue Segment", "Revenue Segment - Staging.Billable Work?"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Revenue Segment - Staging", each true),
#"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Relative Week", each let
CurrentDate = Date.From(DateTime.LocalNow()), // Convert current DateTime to Date
CurrentWeekStart = Date.StartOfWeek(CurrentDate, Day.Monday), // Start of the current week
PreviousWeekStart = Date.AddWeeks(CurrentWeekStart, -1), // Start of the previous week
PreviousWeekEnd = Date.AddDays(CurrentWeekStart, -1), // End of the previous week
DateToCheck = Date.From([Date]) // Convert the DateTime column to Date
in
if DateToCheck >= CurrentWeekStart and DateToCheck <= Date.EndOfWeek(CurrentDate, Day.Monday) then "Current Week"
else if DateToCheck >= PreviousWeekStart and DateToCheck <= PreviousWeekEnd then "Last Week"
else null),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Relative Month", each let
CurrentDate = Date.From(DateTime.LocalNow()), // Get the current date
StartOfCurrentMonth = Date.StartOfMonth(CurrentDate), // Start of the current month
StartOfPreviousMonth = Date.AddMonths(StartOfCurrentMonth, -1), // Start of the previous month
EndOfPreviousMonth = Date.AddDays(StartOfCurrentMonth, -1), // End of the previous month
DateToCheck = Date.From([Date]) // Convert the DateTime column to Date
in
if DateToCheck >= StartOfCurrentMonth and DateToCheck <= Date.EndOfMonth(CurrentDate) then "Current Month"
else if DateToCheck >= StartOfPreviousMonth and DateToCheck <= EndOfPreviousMonth then "Last Month"
else null),

#"Added Custom" = Table.AddColumn(#"Added Custom4", "Month Hours", each let
StartDate = [Start of Month], // Replace with your actual start date column
EndDate = [End of Month], // Replace with your actual end date column
ListOfDates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
WorkDays = List.Select(ListOfDates, each Date.DayOfWeek(_, Day.Monday) < 5), // Exclude weekends
TotalWorkDays = List.Count(WorkDays), // Count the number of workdays
TotalWorkHours = TotalWorkDays * 8 // Multiply workdays by 8 to get total working hours
in
TotalWorkHours),

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Engineering Allocation - FTE", each Value.Divide([Hours], [Month Hours])),
#"Rounded Off" = Table.TransformColumns(#"Added Custom1",{{"Hours", each Number.Round(_, 2), type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Rounded Off",{{"Engineering Allocation - FTE", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Revenue Segment - Staging.Billable Work?", "Billable Work?"}, {"Revenue Segment - Staging.Revenue Segment", "Revenue Segment - Monday"}, {"User Resource Title and Dept - staging.Resource Title", "Resource Title"}, {"User Resource Title and Dept - staging.Resource Dept.", "Resource Department"}, {"User Resource Title and Dept - staging.Employment Status", "Employment Status"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns", {"Project Name"}, Table.Buffer(#"ORS Projects"), {"Clockify Project Name"}, "ORS Projects", JoinKind.LeftOuter),
#"Expanded ORS Projects" = Table.ExpandTableColumn(#"Merged Queries2", "ORS Projects", {"Retainer Cycle", "Monthly Retainer Start Date"}, {"ORS Projects.Retainer Cycle", "ORS Projects.Monthly Retainer Start Date"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded ORS Projects",{{"ORS Projects.Retainer Cycle", "ORS - Retainer Cycle"}, {"ORS Projects.Monthly Retainer Start Date", "ORS - Cutoff Date"}}),

// Add custom logic for "MidMonth Date"
#"Added MidMonth Date" = Table.AddColumn(#"Renamed Columns1", "ORS - MidMonth Date", each if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day([Date]) >= [#"ORS - Cutoff Date"] and Date.Month([Date]) = 12 then
Date.FromText(Text.From(Date.Year([Date]) + 1) & "-01-01")
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day([Date]) >= [#"ORS - Cutoff Date"] then
Date.FromText(Text.From(Date.Year([Date])) & "-" & Text.From(Date.Month([Date]) + 1) & "-01")
else [Date]),

// Add custom logic for "Previous or Current month" column
#"Added Custom2" = Table.AddColumn(#"Added MidMonth Date", "ORS - Previous or Current month", each if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = (Date.Month([#"ORS - MidMonth Date"]) + 1) then ""
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = (Date.Month([#"ORS - MidMonth Date"]) - 1) then "Current Month"
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = Date.Month([#"ORS - MidMonth Date"]) and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) then "Current Month"
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = 12 and Date.Month([#"ORS - MidMonth Date"]) = 1 and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) - 1 then "Current Month"
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Day([#"ORS - MidMonth Date"]) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = Date.Month([#"ORS - MidMonth Date"]) and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) then "Previous Month"
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = Date.Month([#"ORS - MidMonth Date"]) + 1 then "Previous Month"
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = 1 and Date.Month([#"ORS - MidMonth Date"]) = 12 and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) - 1 then "Previous Month"
else if [#"ORS - Retainer Cycle"] = "Calendar Month" and Date.Month([Date]) = Date.Month(DateTime.LocalNow()) and Date.Year([Date]) = Date.Year(DateTime.LocalNow()) then "Current Month"
else if ([#"ORS - Retainer Cycle"] = "Calendar Month" and Date.Month([Date]) = Date.Month(DateTime.LocalNow()) - 1 and Date.Year([Date]) = Date.Year(DateTime.LocalNow()))
or ([#"ORS - Retainer Cycle"] = "Calendar Month" and Date.Month([Date]) = 12 and Date.Month(DateTime.LocalNow()) = 1 and Date.Year([Date]) = Date.Year(DateTime.LocalNow()) - 1) then "Previous Month"
else ""),

// Add Month_Start column based on your logic
#"Added Month Start" = Table.AddColumn(#"Added Custom2", "ORS - Month Start", each if [#"ORS - Retainer Cycle"] = "Calendar Month" and ([#"ORS - Previous or Current month"] = "Current Month" or [#"ORS - Previous or Current month"] = "Previous Month") then
#date(Date.Year([Date]), Date.Month([Date]), 1)
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Current Month" and [#"ORS - MidMonth Date"] = [Date] and Date.Month([#"ORS - MidMonth Date"]) = 1 then
#date(Date.Year([#"ORS - MidMonth Date"]) - 1, 12, [#"ORS - Cutoff Date"])
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Current Month" and [Date] < [#"ORS - MidMonth Date"] then
#date(Date.Year([Date]), Date.Month([Date]), [#"ORS - Cutoff Date"])
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Previous Month" and [#"ORS - MidMonth Date"] = [Date] and Date.Month([#"ORS - MidMonth Date"]) = 1 then
#date(Date.Year([#"ORS - MidMonth Date"]) - 1, 12, [#"ORS - Cutoff Date"])
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Previous Month" and [#"ORS - MidMonth Date"] = [Date] then
#date(Date.Year([#"ORS - MidMonth Date"]), Date.Month([#"ORS - MidMonth Date"]) - 1, [#"ORS - Cutoff Date"])
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Previous Month" and [Date] < [#"ORS - MidMonth Date"] then
#date(Date.Year([Date]), Date.Month([Date]), [#"ORS - Cutoff Date"])
else null),
#"Added Custom5" = Table.AddColumn(#"Added Month Start", "IsWorkday", each if Date.DayOfWeek([Date], Day.Monday) < 5 then 1 else 0),

#"Sorted Rows" = Table.Sort(#"Added Custom5",{{"Date", Order.Descending}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type date}}),

#"Added Month End" = Table.AddColumn(#"Changed Type1", "ORS - Month End", each if [#"ORS - Retainer Cycle"] = "Calendar Month" and ([#"ORS - Previous or Current month"] = "Current Month" or [#"ORS - Previous or Current month"] = "Previous Month") then
Date.EndOfMonth([Date])
else if [#"ORS - Retainer Cycle"] = "Mid-Month" and ([#"ORS - Previous or Current month"] = "Current Month" or [#"ORS - Previous or Current month"] = "Previous Month") then
#date(Date.Year([#"ORS - MidMonth Date"]), Date.Month([#"ORS - MidMonth Date"]), [#"ORS - Cutoff Date"] - 1)
else
null),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Month End",{{"ORS - Month Start", type date}, {"ORS - Month End", type date}, {"Start of Month", type date}, {"End of Month", type date}}),
#"Added Custom6" = Table.AddColumn(#"Changed Type2", "ORS - Inclusion", each if [User Name] = "User1" or [User Name] = "User2" or ([User Name] = "User3" and Text.Contains([Project Name], "ENG-2023-141")) then 1 else 0),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom6", each ([Project Name] <> null)),
#"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows1",{{"ORS - MidMonth Date", type date}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type3", {"Detailed Report ID"})
in
#"Removed Duplicates"

let Source = Table.Buffer(#"Detailed Report - Staging"), #"Expanded Custom Fields" = Table.ExpandListColumn(Source, "Custom Fields"), #"Expanded Custom Fields1" = Table.ExpandRecordColumn(#"Expanded Custom Fields", "Custom Fields", {"customFieldId", "value"}, {"Custom Fields.customFieldId", "Custom Fields.value"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom Fields1",null,"test",Replacer.ReplaceValue,{"Custom Fields.customFieldId"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"Custom Fields.customFieldId"]), "Custom Fields.customFieldId", "Custom Fields.value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"614b79ce8fc8f836af593b78", "614dce95b49ce00a90d4c54b", "test"}), #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"614b3a5a41f10a506b8d1fdf", "Clockify Custom Project Number"}, {"60aff4741a4155464943ab9b", "Clockify Custom Revenue Segment"}}), #"Appended Query" = Table.Combine({#"Renamed Columns2", #"ORS Projects - Dummy Rows"}), #"Sorted Rows1" = Table.Sort(#"Appended Query",{{"Date", Order.Descending}}), #"Merged Queries" = Table.NestedJoin(#"Sorted Rows1", {"User Name"}, Table.Buffer(#"User Resource Title and Dept - staging"), {"Column1.items_page.items.name"}, "User Resource Title and Dept - staging", JoinKind.LeftOuter), #"Expanded User Resource Title and Dept - staging" = Table.ExpandTableColumn(#"Merged Queries", "User Resource Title and Dept - staging", {"Resource Title", "Resource Dept.", "Employment Status"}, {"User Resource Title and Dept - staging.Resource Title", "User Resource Title and Dept - staging.Resource Dept.", "User Resource Title and Dept - staging.Employment Status"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded User Resource Title and Dept - staging", {"Project Name"}, Table.Buffer(#"Revenue Segment - Staging"), {"Name"}, "Revenue Segment - Staging", JoinKind.LeftOuter), #"Expanded Revenue Segment - Staging" = Table.ExpandTableColumn(#"Merged Queries1", "Revenue Segment - Staging", {"Revenue Segment", "Billable Work?"}, {"Revenue Segment - Staging.Revenue Segment", "Revenue Segment - Staging.Billable Work?"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Revenue Segment - Staging", each true), #"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Relative Week", each let CurrentDate = Date.From(DateTime.LocalNow()), // Convert current DateTime to Date CurrentWeekStart = Date.StartOfWeek(CurrentDate, Day.Monday), // Start of the current week PreviousWeekStart = Date.AddWeeks(CurrentWeekStart, -1), // Start of the previous week PreviousWeekEnd = Date.AddDays(CurrentWeekStart, -1), // End of the previous week DateToCheck = Date.From([Date]) // Convert the DateTime column to Date in if DateToCheck >= CurrentWeekStart and DateToCheck <= Date.EndOfWeek(CurrentDate, Day.Monday) then "Current Week" else if DateToCheck >= PreviousWeekStart and DateToCheck <= PreviousWeekEnd then "Last Week" else null), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Relative Month", each let CurrentDate = Date.From(DateTime.LocalNow()), // Get the current date StartOfCurrentMonth = Date.StartOfMonth(CurrentDate), // Start of the current month StartOfPreviousMonth = Date.AddMonths(StartOfCurrentMonth, -1), // Start of the previous month EndOfPreviousMonth = Date.AddDays(StartOfCurrentMonth, -1), // End of the previous month DateToCheck = Date.From([Date]) // Convert the DateTime column to Date in if DateToCheck >= StartOfCurrentMonth and DateToCheck <= Date.EndOfMonth(CurrentDate) then "Current Month" else if DateToCheck >= StartOfPreviousMonth and DateToCheck <= EndOfPreviousMonth then "Last Month" else null), #"Added Custom" = Table.AddColumn(#"Added Custom4", "Month Hours", each let StartDate = [Start of Month], // Replace with your actual start date column EndDate = [End of Month], // Replace with your actual end date column ListOfDates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)), WorkDays = List.Select(ListOfDates, each Date.DayOfWeek(_, Day.Monday) < 5), // Exclude weekends TotalWorkDays = List.Count(WorkDays), // Count the number of workdays TotalWorkHours = TotalWorkDays * 8 // Multiply workdays by 8 to get total working hours in TotalWorkHours), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Engineering Allocation - FTE", each Value.Divide([Hours], [Month Hours])), #"Rounded Off" = Table.TransformColumns(#"Added Custom1",{{"Hours", each Number.Round(_, 2), type number}}), #"Changed Type" = Table.TransformColumnTypes(#"Rounded Off",{{"Engineering Allocation - FTE", type number}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Revenue Segment - Staging.Billable Work?", "Billable Work?"}, {"Revenue Segment - Staging.Revenue Segment", "Revenue Segment - Monday"}, {"User Resource Title and Dept - staging.Resource Title", "Resource Title"}, {"User Resource Title and Dept - staging.Resource Dept.", "Resource Department"}, {"User Resource Title and Dept - staging.Employment Status", "Employment Status"}}), #"Merged Queries2" = Table.NestedJoin(#"Renamed Columns", {"Project Name"}, Table.Buffer(#"ORS Projects"), {"Clockify Project Name"}, "ORS Projects", JoinKind.LeftOuter), #"Expanded ORS Projects" = Table.ExpandTableColumn(#"Merged Queries2", "ORS Projects", {"Retainer Cycle", "Monthly Retainer Start Date"}, {"ORS Projects.Retainer Cycle", "ORS Projects.Monthly Retainer Start Date"}), #"Renamed Columns1" = Table.RenameColumns(#"Expanded ORS Projects",{{"ORS Projects.Retainer Cycle", "ORS - Retainer Cycle"}, {"ORS Projects.Monthly Retainer Start Date", "ORS - Cutoff Date"}}), // Add custom logic for "MidMonth Date" #"Added MidMonth Date" = Table.AddColumn(#"Renamed Columns1", "ORS - MidMonth Date", each if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day([Date]) >= [#"ORS - Cutoff Date"] and Date.Month([Date]) = 12 then Date.FromText(Text.From(Date.Year([Date]) + 1) & "-01-01") else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day([Date]) >= [#"ORS - Cutoff Date"] then Date.FromText(Text.From(Date.Year([Date])) & "-" & Text.From(Date.Month([Date]) + 1) & "-01") else [Date]), // Add custom logic for "Previous or Current month" column #"Added Custom2" = Table.AddColumn(#"Added MidMonth Date", "ORS - Previous or Current month", each if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = (Date.Month([#"ORS - MidMonth Date"]) + 1) then "" else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = (Date.Month([#"ORS - MidMonth Date"]) - 1) then "Current Month" else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = Date.Month([#"ORS - MidMonth Date"]) and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) then "Current Month" else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = 12 and Date.Month([#"ORS - MidMonth Date"]) = 1 and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) - 1 then "Current Month" else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) >= [#"ORS - Cutoff Date"] and Date.Day([#"ORS - MidMonth Date"]) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = Date.Month([#"ORS - MidMonth Date"]) and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) then "Previous Month" else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = Date.Month([#"ORS - MidMonth Date"]) + 1 then "Previous Month" else if [#"ORS - Retainer Cycle"] = "Mid-Month" and Date.Day(DateTime.LocalNow()) < [#"ORS - Cutoff Date"] and Date.Month(DateTime.LocalNow()) = 1 and Date.Month([#"ORS - MidMonth Date"]) = 12 and Date.Year(DateTime.LocalNow()) = Date.Year([#"ORS - MidMonth Date"]) - 1 then "Previous Month" else if [#"ORS - Retainer Cycle"] = "Calendar Month" and Date.Month([Date]) = Date.Month(DateTime.LocalNow()) and Date.Year([Date]) = Date.Year(DateTime.LocalNow()) then "Current Month" else if ([#"ORS - Retainer Cycle"] = "Calendar Month" and Date.Month([Date]) = Date.Month(DateTime.LocalNow()) - 1 and Date.Year([Date]) = Date.Year(DateTime.LocalNow())) or ([#"ORS - Retainer Cycle"] = "Calendar Month" and Date.Month([Date]) = 12 and Date.Month(DateTime.LocalNow()) = 1 and Date.Year([Date]) = Date.Year(DateTime.LocalNow()) - 1) then "Previous Month" else ""), // Add Month_Start column based on your logic #"Added Month Start" = Table.AddColumn(#"Added Custom2", "ORS - Month Start", each if [#"ORS - Retainer Cycle"] = "Calendar Month" and ([#"ORS - Previous or Current month"] = "Current Month" or [#"ORS - Previous or Current month"] = "Previous Month") then #date(Date.Year([Date]), Date.Month([Date]), 1) else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Current Month" and [#"ORS - MidMonth Date"] = [Date] and Date.Month([#"ORS - MidMonth Date"]) = 1 then #date(Date.Year([#"ORS - MidMonth Date"]) - 1, 12, [#"ORS - Cutoff Date"]) else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Current Month" and [Date] < [#"ORS - MidMonth Date"] then #date(Date.Year([Date]), Date.Month([Date]), [#"ORS - Cutoff Date"]) else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Previous Month" and [#"ORS - MidMonth Date"] = [Date] and Date.Month([#"ORS - MidMonth Date"]) = 1 then #date(Date.Year([#"ORS - MidMonth Date"]) - 1, 12, [#"ORS - Cutoff Date"]) else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Previous Month" and [#"ORS - MidMonth Date"] = [Date] then #date(Date.Year([#"ORS - MidMonth Date"]), Date.Month([#"ORS - MidMonth Date"]) - 1, [#"ORS - Cutoff Date"]) else if [#"ORS - Retainer Cycle"] = "Mid-Month" and [#"ORS - Previous or Current month"] = "Previous Month" and [Date] < [#"ORS - MidMonth Date"] then #date(Date.Year([Date]), Date.Month([Date]), [#"ORS - Cutoff Date"]) else null), #"Added Custom5" = Table.AddColumn(#"Added Month Start", "IsWorkday", each if Date.DayOfWeek([Date], Day.Monday) < 5 then 1 else 0), #"Sorted Rows" = Table.Sort(#"Added Custom5",{{"Date", Order.Descending}}), #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type date}}), #"Added Month End" = Table.AddColumn(#"Changed Type1", "ORS - Month End", each if [#"ORS - Retainer Cycle"] = "Calendar Month" and ([#"ORS - Previous or Current month"] = "Current Month" or [#"ORS - Previous or Current month"] = "Previous Month") then Date.EndOfMonth([Date]) else if [#"ORS - Retainer Cycle"] = "Mid-Month" and ([#"ORS - Previous or Current month"] = "Current Month" or [#"ORS - Previous or Current month"] = "Previous Month") then #date(Date.Year([#"ORS - MidMonth Date"]), Date.Month([#"ORS - MidMonth Date"]), [#"ORS - Cutoff Date"] - 1) else null), #"Changed Type2" = Table.TransformColumnTypes(#"Added Month End",{{"ORS - Month Start", type date}, {"ORS - Month End", type date}, {"Start of Month", type date}, {"End of Month", type date}}), #"Added Custom6" = Table.AddColumn(#"Changed Type2", "ORS - Inclusion", each if [User Name] = "User1" or [User Name] = "User2" or ([User Name] = "User3" and Text.Contains([Project Name], "ENG-2023-141")) then 1 else 0), #"Filtered Rows1" = Table.SelectRows(#"Added Custom6", each ([Project Name] <> null)), #"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows1",{{"ORS - MidMonth Date", type date}}), #"Removed Duplicates" = Table.Distinct(#"Changed Type3", {"Detailed Report ID"}) in #"Removed Duplicates"

Revenue Segment - Staging

Spoiler

let
Source = Table.Buffer(#"Revenue Segment - Load")
in
Source

Revenue Segment - Load

Spoiler

let
BoardID = 5156139052,

// Page 1
#"1st_Page_Table" = Table.Buffer(GetBoard_1stPage_subitems(BoardID)),
#"1st_Page_Table_Updated" = Table.ReplaceValue(#"1st_Page_Table", null, "NoMorePages", Replacer.ReplaceValue, {"Column1.items_page.cursor"}),
#"2nd_page_cursor" = #"1st_Page_Table_Updated"{0}[Column1.items_page.cursor],

// Page 2
#"2nd_Page_Table" = if #"2nd_page_cursor" <> "NoMorePages" then Table.Buffer(GetBoard_RemainingPages_subitems(#"2nd_page_cursor")) else EmptyTable(),
#"2nd_Page_Table_Updated" = Table.ReplaceValue(#"2nd_Page_Table", null, "NoMorePages", Replacer.ReplaceValue, {"Column1.items_page.cursor"}),
#"3rd_page_cursor" = #"2nd_Page_Table_Updated"{0}[Column1.items_page.cursor],

// Page 3
#"3rd_Page_Table" = if #"3rd_page_cursor" <> "NoMorePages" then Table.Buffer(GetBoard_RemainingPages_subitems(#"3rd_page_cursor")) else EmptyTable(),
#"3rd_Page_Table_Updated" = Table.ReplaceValue(#"3rd_Page_Table", null, "NoMorePages", Replacer.ReplaceValue, {"Column1.items_page.cursor"}),

// Combine and buffer the full set
Combined_Table1 = Table.Combine({
#"1st_Page_Table_Updated",
#"2nd_Page_Table_Updated",
#"3rd_Page_Table_Updated"
}),

#"Filtered Rows" = Table.SelectRows(Combined_Table1, each ([Name] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {
"Column1.items_page.items.id",
"Column1.items_page.items.name",
"Column1.items_page.items.parent_item.id",
"Revenue Segment",
"Billable Work?",
"Monday Name",
"Proposal Number - QBO join"
}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", {
{"Column1.items_page.items.name", "Name"}
})
in
#"Renamed Columns"

let BoardID = 5156139052, // Page 1 #"1st_Page_Table" = Table.Buffer(GetBoard_1stPage_subitems(BoardID)), #"1st_Page_Table_Updated" = Table.ReplaceValue(#"1st_Page_Table", null, "NoMorePages", Replacer.ReplaceValue, {"Column1.items_page.cursor"}), #"2nd_page_cursor" = #"1st_Page_Table_Updated"{0}[Column1.items_page.cursor], // Page 2 #"2nd_Page_Table" = if #"2nd_page_cursor" <> "NoMorePages" then Table.Buffer(GetBoard_RemainingPages_subitems(#"2nd_page_cursor")) else EmptyTable(), #"2nd_Page_Table_Updated" = Table.ReplaceValue(#"2nd_Page_Table", null, "NoMorePages", Replacer.ReplaceValue, {"Column1.items_page.cursor"}), #"3rd_page_cursor" = #"2nd_Page_Table_Updated"{0}[Column1.items_page.cursor], // Page 3 #"3rd_Page_Table" = if #"3rd_page_cursor" <> "NoMorePages" then Table.Buffer(GetBoard_RemainingPages_subitems(#"3rd_page_cursor")) else EmptyTable(), #"3rd_Page_Table_Updated" = Table.ReplaceValue(#"3rd_Page_Table", null, "NoMorePages", Replacer.ReplaceValue, {"Column1.items_page.cursor"}), // Combine and buffer the full set Combined_Table1 = Table.Combine({ #"1st_Page_Table_Updated", #"2nd_Page_Table_Updated", #"3rd_Page_Table_Updated" }), #"Filtered Rows" = Table.SelectRows(Combined_Table1, each ([Name] <> null)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", { "Column1.items_page.items.id", "Column1.items_page.items.name", "Column1.items_page.items.parent_item.id", "Revenue Segment", "Billable Work?", "Monday Name", "Proposal Number - QBO join" }), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns", { {"Column1.items_page.items.name", "Name"} }) in #"Renamed Columns"

Like I said, this randomly stopped working a month ago, unprompted by changes and I'm at a complete loss as to what else to try.

v-karpurapud · ‎07-28-2025

Hi @regnidem16

Thank you for contacting the Microsoft Fabric Community Forum.

The DM_GWPipeline_Gateway_MashupDataAccessError arises in Power BI Service due to strict data privacy enforcement when combining data from multiple sources, especially involving external web APIs. In this scenario, the issue is linked to the Revenue Segment - Load query, which contains steps still being dynamically evaluated during a merge in another query. This behavior conflicts with the mashup engine’s privacy model, even if intermediate buffering is applied.

To address this, ensure that the final output of the Revenue Segment - Load query is explicitly buffered before being referenced elsewhere. This can be achieved by wrapping the final step with Table.Buffer() and assigning it to a variable (e.g., BufferedFinal = Table.Buffer(#"Renamed Columns")), then returning this variable. This approach fully materializes the API data prior to any merge operations, preventing re-evaluation at refresh time.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

View solution in original post

v-karpurapud · ‎08-06-2025

Hi @regnidem16

I would like to inquire if the issue has been resolved. If not, kindly provide additional details so we can offer further assistance.

Thank you.

v-karpurapud · ‎08-03-2025

Hi @regnidem16

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

v-karpurapud · ‎07-30-2025

Hi @regnidem16

We have not yet received a response from you regarding your query. If the previous response was helpful, please let us know. If not, feel free to provide more details so we can assist you further.

Thank you.

v-karpurapud · ‎07-28-2025

Hi @regnidem16

Thank you for contacting the Microsoft Fabric Community Forum.

The DM_GWPipeline_Gateway_MashupDataAccessError arises in Power BI Service due to strict data privacy enforcement when combining data from multiple sources, especially involving external web APIs. In this scenario, the issue is linked to the Revenue Segment - Load query, which contains steps still being dynamically evaluated during a merge in another query. This behavior conflicts with the mashup engine’s privacy model, even if intermediate buffering is applied.

To address this, ensure that the final output of the Revenue Segment - Load query is explicitly buffered before being referenced elsewhere. This can be achieved by wrapping the final step with Table.Buffer() and assigning it to a variable (e.g., BufferedFinal = Table.Buffer(#"Renamed Columns")), then returning this variable. This approach fully materializes the API data prior to any merge operations, preventing re-evaluation at refresh time.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

Royel · ‎07-27-2025

Hay @regnidem16 I can feel you have gone through so far.

I am just wantering do you get the same results for privacy level as Private?

In general when Data source credentials is none, it through data meshup access error. However, since you are using external APIs you can try with privacy level Public.

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

regnidem16 · ‎07-27-2025

Yes, we have tried setting the privacy level to public and had the same result.

Greg_Deckler · ‎07-25-2025

@regnidem16 Sounds like you have tried most of the usual suspects. I would recommend opening a support case with Microsoft.

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

regnidem16 · ‎07-25-2025

Are they typically responsive for things like this? Microsft, at least for this sort of thing, strike me as a modern tech company where they develop a product and then direct users to the community for any sort of tech support (see: this board).

Unsolveable Mashup Data Access Error

Helpful resources

Power BI Monthly Update - November 2025

Fabric Data Days

FabCon Atlanta 2026

FabCon is coming to Atlanta

Unsolveable Mashup Data Access Error

Helpful resources

Power BI Monthly Update - November 2025

Fabric Data Days

FabCon Atlanta 2026