Can't refresh the Dataset on Power BI Service.

[Anonymous]

Hi, I have created one dataset which connects to Dynamic CRM. I have loaded 2 tables into it and then merged one table value to another one and then disabling the load for the one I don't want to see in Power BI Desktop. I have then published the dataset to Power BI Service everything is working fine up until this point but then when I am trying to refresh the dataset on Power Bi service it's throwing an error saying "[Unable to combine data] Section1/Appointments/Changed Type references other queries or steps, so it may not directly access a data source. Please rebuild this data combination". Not too sure what is happening. Below is the M-code.

let
Source = OData.Feed(ServiceRootURL, null, [Implementation="2.0"]),
entity_table = Source{[Name = "appointments", Signature = "table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(entity_table,{"createdon", "activityid", "scheduledend", "scheduleddurationminutes", "_regardingobjectid_value", "scheduledstart", "subject", "_createdby_value", "_ownerid_value", "usccg_supportrequired", "usccg_appointmentno", "_usccg_contactid_value", "usccg_meetingnotes", "usccg_nextstepduedate", "usccg_rescheduledcount", "usccg_additionalcancellationdetails", "usccg_premeetingfunnelstage", "usccg_leveltitle", "usccg_nextstep", "usccg_createnewtaskfornextsteps", "_usccg_opportunityid_value", "usccg_cancellationreason", "usccg_internalnotes", "usccg_salesprocessstage", "usccg_typeofsupportrequired", "usccg_appttype", "_usccg_accountid_value", "usccg_uscsubject", "usccg_outcome", "usccg_synctooutlook", "_usccg_nextstepcontactid_value", "_usccg_setby_value", "usccg_rescheduled"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"activityid"}, #"Activity Party", {"Activty ID"}, "Activity Party", JoinKind.LeftOuter),
#"Expanded Activity Party" = Table.ExpandTableColumn(#"Merged Queries", "Activity Party", {"Party ID"}, {"Party ID"}),
#"Removed Duplicates" = Table.Distinct(#"Expanded Activity Party"),
#"Split Column by Delimiter2" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Duplicates", {{"usccg_nextstepduedate", type text}}, "en-CA"), "usccg_nextstepduedate", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"usccg_nextstepduedate.1", "usccg_nextstepduedate.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"usccg_nextstepduedate.1", type date}, {"usccg_nextstepduedate.2", type time}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type2", "Support Required", each if [usccg_supportrequired] = true then "Yes" else "No"),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Appt No", each if [usccg_appointmentno] = 1 then "M1" else if [usccg_appointmentno] = 2 then "M2" else if [usccg_appointmentno] = 3 then "M3" else if [usccg_appointmentno] = 4 then "M4" else if [usccg_appointmentno] = 5 then "M5" else if [usccg_appointmentno] = 6 then "M6" else if [usccg_appointmentno] = 7 then "M7" else if [usccg_appointmentno] = 8 then "M8+" else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Pre Mtg Funnel Stage", each if [usccg_premeetingfunnelstage] = 1 then "Active Prospect" else if [usccg_premeetingfunnelstage] = 2 then "Lead" else if [usccg_premeetingfunnelstage] = 3 then "Opportunity" else if [usccg_premeetingfunnelstage] = 4 then "Diagnostic" else if [usccg_premeetingfunnelstage] = 5 then "Project" else if [usccg_premeetingfunnelstage] = 99 then "Drop - do not pursue" else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Level/Title", each if [usccg_leveltitle] = 214910000 then "C-Suite" else if [usccg_leveltitle] = 214910001 then "Calendar Management" else if [usccg_leveltitle] = 214910002 then "Director" else if [usccg_leveltitle] = 214910003 then "Other" else if [usccg_leveltitle] = 214910004 then "VP" else if [usccg_leveltitle] = 214910005 then "EVP" else if [usccg_leveltitle] = 214910006 then "SVP" else null),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Create New Task for Next Steps", each if [usccg_createnewtaskfornextsteps] = false then "No" else "Yes"),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Cancellation Reason", each if [usccg_cancellationreason] = 214910000 then "Unknown" else if [usccg_cancellationreason] = 214910001 then "Change of Heart" else if [usccg_cancellationreason] = 214910002 then "Schedule Conflict" else if [usccg_cancellationreason] = 214910003 then "Time Travel and/or Expense" else if [usccg_cancellationreason] = 214910004 then "Weather" else if [usccg_cancellationreason] = 214910099 then "Other" else null),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Appointment Type", each if [usccg_appttype] = 214910000 then "Sales Call" else if [usccg_appttype] = 214910001 then "Sales Meeting" else if [usccg_appttype] = 214910002 then "Web Meeting" else if [usccg_appttype] = 214910003 then "Pre Analysis" else if [usccg_appttype] = 214910004 then "Walk Through" else if [usccg_appttype] = 214910005 then "Calendar Management" else if [usccg_appttype] = 214910006 then "Other" else null),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "Outcome", each if [usccg_outcome] = 214910000 then "Pending" else if [usccg_outcome] = 214910001 then "Completed – F/U Set" else if [usccg_outcome] = 214910002 then "Completed – No F/U Set" else if [usccg_outcome] = 214910003 then "CXL – Client" else if [usccg_outcome] = 214910004 then "CXL – USC" else if [usccg_outcome] = 214910005 then "Calendar Management" else null),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "Sync to Outlook", each if [usccg_synctooutlook] = false then "No" else "Yes"),
#"Added Conditional Column9" = Table.AddColumn(#"Added Conditional Column8", "Rescheduled", each if [usccg_rescheduled] = false then "No" else "Yes"),
#"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column9", "Duration (mins)", each Text.Combine({Text.From([scheduleddurationminutes], "en-CA"), " minutes"}), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"usccg_supportrequired", "usccg_appointmentno", "usccg_premeetingfunnelstage", "usccg_leveltitle", "usccg_createnewtaskfornextsteps", "usccg_cancellationreason", "usccg_appttype", "usccg_outcome", "usccg_synctooutlook", "usccg_rescheduled", "usccg_nextstepduedate.2", "scheduleddurationminutes"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "createdon", "createdon - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "scheduledstart", "scheduledstart - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column1",{{"subject", "Oulook Subject"}, {"_createdby_value", "Created By ID"}, {"_ownerid_value", "Owner ID"}, {"_usccg_contactid_value", "Contact ID"}, {"usccg_meetingnotes", "Meeting Notes"}, {"usccg_nextstepduedate.1", "Next Step Due Date"}, {"usccg_rescheduledcount", "Rescheduled Count"}, {"usccg_additionalcancellationdetails", "Other Cancellation Reason Details"}, {"usccg_nextstep", "Next Step"}, {"_usccg_opportunityid_value", "Opportunity ID"}, {"usccg_salesprocessstage", "Sales Process Stage"}, {"usccg_typeofsupportrequired", "Type of Support Required"}, {"_usccg_accountid_value", "Account ID"}, {"usccg_uscsubject", "USC Subject"}, {"_usccg_nextstepcontactid_value", "Next Step Contact ID"}, {"_usccg_setby_value", "Set By ID"}, {"_regardingobjectid_value", "Regarding ID"}, {"createdon", "Created On DateTime"}, {"createdon - Copy", "Created On Date"}, {"scheduledstart", "Start Date and Time"}, {"scheduledend", "End Date and Time"}, {"Duration (mins)", "Duration"}, {"scheduledstart - Copy", "Start Date"}, {"usccg_internalnotes", "Internal Notes"}, {"Party ID", "Set For ID"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Created On DateTime", type datetime}, {"End Date and Time", type datetime}, {"Support Required", type text}, {"Appt No", type text}, {"Pre Mtg Funnel Stage", type text}, {"Level/Title", type text}, {"Create New Task for Next Steps", type text}, {"Cancellation Reason", type text}, {"Appointment Type", type text}, {"Outcome", type text}, {"Sync to Outlook", type text}, {"Rescheduled", type text}, {"Created On Date", type date}, {"Start Date", type date}})
in
#"Changed Type"

Any guidance would be appreciated.

Thanks
Roshan

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

Please firstly change the privacy level to none and check again.

And looking at the error message, we can understand that it is related to having reference to two different sets of data sources in query. Power Query engine is not allowed to access two different data sources originating from different queries in the same step – as far as I understand it this is because it makes it too hard for the engine to work out whether a step connects to a data source or not, and so which data privacy rules should be applied.

I came across this interesting post from Ken Puls which talks about a similar issue: Power Query Errors: Please Rebuild This Data Combination  The solution suggested is to stage them separately and then use the staged tables to combine datasets.

Refer to :

Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error

https://community.powerbi.com/t5/Service/Unable-to-combine-data-Please-rebuild-this-data-combination/m-p/1642440

https://community.powerbi.com/t5/Service/Error-Unable-to-combine-data-Sorry-there-was-an-error-while/m-p/1096624

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.