Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm building a dataflow that links to other dataflows for and merges or appends those tables where required. The structure of this is shown below. The blue blocks are the data sources, yellow blocks are dataflows that do not use a gateway (i.e. all data sources are cloud based), orange blocks are dataflows that use a on-prem gateway.
The completed data will be in the Surveys dataflow. This all works up until a point, when I get an error. The Surveys dataflow contains the following entities structured in the follow way;
The blue blocks are Linked Entities and have the Enable Load option unticked, the green are entities computed from the linked entities and will have the Enable Load option ticked, these are the entities people will be using in their reports.
The final entity with a red border is the problem. If I untick Enable Load on KPIs and KPIAmendments then the dataflow refresh fails, if I have the Enable Load option on those tables ticked then the dataflow refresh works. Why is this failing when all the other linked entities can have the Enable Load option unticked and the dataflow refreshes fine?
The error I get is;
Run time | Dataflow name | Dataflow refresh status | Entity name | Start time | End time | Entity refresh status | Error |
16/05/2019 10:02 | Surveys | Failed | Rant&RaveUKIBase | 16/05/2019 10:02 | 16/05/2019 10:07 | Completed | |
16/05/2019 10:02 | Surveys | Failed | Rant&RaveGlobalBase | 16/05/2019 10:02 | 16/05/2019 10:03 | Completed | |
16/05/2019 10:02 | Surveys | Failed | Rant&RaveCombinedBase | 16/05/2019 10:07 | 16/05/2019 10:08 | Completed | |
16/05/2019 10:02 | Surveys | Failed | Rant&RaveCombinedSentiment | 16/05/2019 10:08 | 16/05/2019 10:08 | Failed | Error: The current operation was canceled because one or more operations failed... RootActivityId = b4d6cf99-3a1e-49f6-ac7d-5ca245b2a048 Request ID: 2c6be4fc-5c93-2e86-bec8-32792d09bccb. |
16/05/2019 10:02 | Surveys | Failed | Rant&RaveCombinedWithTargets | 16/05/2019 10:08 | 16/05/2019 10:08 | Failed | Error: An internal error occurred... RootActivityId = b4d6cf99-3a1e-49f6-ac7d-5ca245b2a048.Param1 = The credentials provided for the PowerBI source are invalid. (Source at PowerBI.) Request ID: 2c6be4fc-5c93-2e86-bec8-32792d09bccb. |
The code for Rant&RaveCombinedWithTargets is as follows;
let Source = #"Rant&RaveCombinedBase", #"Added KPIAmendmentsProductKey" = Table.AddColumn(Source, "KPIAmendmentsProductKey", each Text.Combine({[EmployeeNumber], Text.From(Number.From(Date.StartOfWeek([Created Date]))), [Product Group]}, "-"), type text), #"Added KPIKey" = Table.AddColumn(#"Added KPIAmendmentsProductKey", "KPIKey", each Text.Combine({[Product Group], [Interaction Type Group], Text.From(Number.From(Date.StartOfMonth([Created Date])))}, "-"), type text), #"Merged KPIAmendments" = Table.NestedJoin(#"Added KPIKey", {"KPIAmendmentsProductKey"}, KPIAmendments, {"KPIAmendmentsKeyProduct"}, "KPIAmendments", JoinKind.LeftOuter), #"Merged KPIs" = Table.NestedJoin(#"Merged KPIAmendments", {"KPIKey"}, KPIs, {"KPIKey"}, "KPIs", JoinKind.LeftOuter), #"Expanded KPIAmendments" = Table.ExpandTableColumn(#"Merged KPIs", "KPIAmendments", {"FCR", "CSAT", "NPS"}, {"KPIAmendments.FCR", "KPIAmendments.CSAT", "KPIAmendments.NPS"}), #"Expanded KPIs" = Table.ExpandTableColumn(#"Expanded KPIAmendments", "KPIs", {"CSAT", "FCR", "NPS"}, {"KPIs.CSAT", "KPIs.FCR", "KPIs.NPS"}), #"Added FCR Target" = Table.AddColumn(#"Expanded KPIs", "FCR Target", each if [KPIAmendments.FCR] = null then [KPIs.FCR] else [KPIAmendments.FCR]), #"Added CSAT Target" = Table.AddColumn(#"Added FCR Target", "CSAT Target", each if [KPIAmendments.CSAT] = null then [KPIs.CSAT] else [KPIAmendments.CSAT]), #"Added NPS Target" = Table.AddColumn(#"Added CSAT Target", "NPS Target", each if [KPIAmendments.NPS] = null then [KPIs.NPS] else [KPIAmendments.NPS]), #"Remove other columns" = Table.SelectColumns(#"Added NPS Target", {"Survey ID", "Survey Type", "Created Date", "Customer Name", "CSAT Score", "CSAT", "FCRValue", "FCR Text", "tNPS Score", "tNPS Text", "tNPSValue", "tNPSSurvey", "Customer Comment", "Customer Comment Categories", "Customer Comment Sentiment Score", "Account Number", "Account Name", "Account Type", "Territory", "Customer Email", "Customer Since", "Customer Tenure", "Serial Number", "Product Name", "Product Group", "Sage Cover Level", "Product Since", "Product Tenure", "Interaction Reference", "Interaction Date", "Interaction Type Group", "Interaction Type", "Interaction Direction", "Interaction Category Tier 1", "Interaction Category Tier 2", "Interaction Category Tier 3", "Interaction Category Combined", "Agent Interaction Notes", "Interaction Duration", "EmployeeNumber", "GlobalSalesforceID", "Agent Name", "Department", "Business Area", "LineupKey", "DateKey", "Interaction Category Tier 4", "Interaction Category Tier 5", "Interaction Category 2 Tier 1", "Interaction Category 2 Tier 2", "Interaction Category 2 Tier 3", "Interaction Category 2 Tier 4", "Interaction Category 2 Tier 5", "Interaction Category 2 Combined", "Interaction Category 3 Tier 1", "Interaction Category 3 Tier 2", "Interaction Category 3 Tier 3", "Interaction Category 3 Tier 4", "Interaction Category 3 Tier 5", "Interaction Category 3 Combined", "FCR Target", "CSAT Target", "NPS Target"}), #"Finalised column types" = Table.TransformColumnTypes(#"Remove other columns", {{"FCR Target", Percentage.Type}, {"CSAT Target", Percentage.Type}, {"NPS Target", Int64.Type}}) in #"Finalised column types"
Hi @mark_carlisle ,
Based on my test, I cannot reproduce your issue here. I suggest you to create an support ticket for help here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.