Data Flow refresh error

[beatrizalbuqu]

Hi,

I've had a refresh error for a dataflow that contains only three queries. I've checked the credentials and they all seem fine.
I also have other data flows accessing the same data source that are working perfectly fine. Can you please help me to figure out what happened here?

I'll paste here the error I get from the refresh history log and then the queries codes. The first query is the only one that loads to the data flow whilst the other ones have their option "Enable load" unmarked.

Error: Request ID: 313103cb-e96b-0357-13bb-894537de33c5 Activity ID: cb710714-a8a0-4d3f-8802-112c72ee8796

Query Project by stage full:

let

Source = SharePoint.Contents("https://wearebulletproof.sharepoint.com/sites/ReportingLondon/", [ApiVersion = 14]),

#"Shared Documents" = Source{[Name = "Documents"]}[Content],

#"POWER BI SOURCES" = #"Shared Documents"{[Name = "POWER BI SOURCES"]}[Content],

MAPPINGS = #"POWER BI SOURCES"{[Name = "MAPPINGS"]}[Content],

Navigation = MAPPINGS{[Name = "SCREENDRAGON_DIMENSIONS"]}[Content],

#"Filtered rows" = Table.SelectRows(Navigation, each [Name] = "Project by Stages"),

#"Expanded Content" = Table.ExpandTableColumn(#"Filtered rows", "Content", {"Content"}, {"Content.1"}),

#"Filtered hidden files" = Table.SelectRows(#"Expanded Content", each [Attributes]?[Hidden]? <> true),

#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content.1])),

#"Removed other columns" = Table.SelectColumns(#"Invoke custom function", {"Transform file"}),

#"Expanded table column <<<" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),

#"Changed Type1" = Table.TransformColumnTypes(#"Expanded table column <<<", {{"Stage Close Date", type date}, {"Stage Start Date", type date}}),

#"- - Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Stage Status"}, Stage_Order, {"StageStatusPK"}, "Stage_Order", JoinKind.LeftOuter),

#"- - Expanded Stage_Order" = Table.ExpandTableColumn(#"- - Merged Queries", "Stage_Order", {"Order"}, {"stage.Order"}),

#"*^* Sort Stage Close Date" = Table.Sort(#"- - Expanded Stage_Order", {{"Stage Close Date", Order.Descending}}),

#"*^* Sort Stage Start Date" = Table.Sort(#"*^* Sort Stage Close Date", {{"Stage Start Date", Order.Descending}}),

#"*^* Removed duplicates" = Table.Distinct(#"*^* Sort Stage Start Date", {"Stage ID"}),

#"Added StageDatesCheck true/false" = Table.AddColumn(#"*^* Removed duplicates", "StageDatesCheck", each if [Stage Start Date] <= [Stage Close Date] then true else false),

#"Replaced errors" = Table.ReplaceErrorValues(#"Added StageDatesCheck true/false", {{"Parent Project ID", null}, {"Stage Close Date", null}, {"Stage ID", null}, {"Stage Name", null}, {"Stage Start Date", null}, {"Stage Status", null}, {"Investment Stage", null}, {"Investment Type", null}, {"Template Stage Name", null}, {"StageDatesCheck", null}}),

#"Added Stage Duration" = Table.AddColumn(#"Replaced errors", "Stage Duration", each if [StageDatesCheck] = true then Duration.Days([Stage Close Date] - [Stage Start Date] ) else 0, type number),

#"Duplicated column - Copy (2)" = Table.DuplicateColumn(#"Added Stage Duration", "Template Stage Name", "Template Stage Name - Copy (2)"),

#"Extracted text before delimiter 1of2" = Table.TransformColumns(#"Duplicated column - Copy (2)", {{"Template Stage Name - Copy (2)", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),

#"Extracted text between delimiters 2of2" = Table.TransformColumns(#"Extracted text before delimiter 1of2", {{"Template Stage Name - Copy (2)", each Text.BetweenDelimiters(Text.From(_), "_", "_", 1, 3), type text}}),

#"Inserted text before delimiter" = Table.AddColumn(#"Extracted text between delimiters 2of2", "Text before delimiter", each Text.BeforeDelimiter([#"Template Stage Name - Copy (2)"], "_", 0), type text),

#"Renamed Columns" = Table.RenameColumns(#"Inserted text before delimiter", {{"Text before delimiter", "CJN PprojID"}, {"Template Stage Name - Copy (2)", "PprojID_Stage00"}}),

#"** Filtered null + blanks" = Table.SelectRows(#"Renamed Columns", each [Template Stage Name] <> "" and [CJN PprojID] <> ""),

#"** quick check Match?" = Table.AddColumn(#"** Filtered null + blanks", "IsMatched", each [Parent Project ID] = [CJN PprojID]),

#"Removed Columns" = Table.RemoveColumns(#"** quick check Match?", {"IsMatched", "Parent Project ID", "StageDatesCheck"}),

#"Changed column type 1" = Table.TransformColumnTypes(#"Removed Columns", {{"Stage ID", Int64.Type}, {"Stage Name", type text}, {"Stage Status", type text}, {"Investment Stage", type text}, {"Investment Type", type text}, {"Template Stage Name", type text}, {"CJN PprojID", Int64.Type}, {"stage.Order", Int64.Type}}),

#"Inserted merged ProjStage Unique" = Table.AddColumn(

#"Changed column type 1",

"ProjStage Unique",

each

let

parts = {

Text.From([PprojID_Stage00]),

Text.From([Stage ID]),

if [Stage Start Date] <> null then Date.ToText([Stage Start Date], "yyyy-MM-dd") else null,

Text.From([stage.Order])

}

in

Text.Combine(List.RemoveNulls(parts), " / "),

type text

),

#"Inserted merged PprojID+StageID" = Table.AddColumn(#"Inserted merged ProjStage Unique", "ProjID+StageID", each Text.Combine({Text.From([CJN PprojID]), Text.From([Stage ID])}, "_"), type text),

#"Merged queries" = Table.NestedJoin(#"Inserted merged PprojID+StageID", {"CJN PprojID"}, Project_SD1, {"Project ID"}, "Project_SD1", JoinKind.LeftOuter),

#"Expanded Project_SD1" = Table.ExpandTableColumn(#"Merged queries", "Project_SD1", {"Region (for projections)"}, {"Region (for projections)"})

in

#"Expanded Project_SD1"

Query Stage_Order:

let

Source = SharePoint.Contents("https://wearebulletproof.sharepoint.com/sites/ReportingLondon/", [ApiVersion = 14]),

Navigation = Source{[Name = "Documents"]}[Content],

#"Navigation 1" = Navigation{[Name = "POWER BI SOURCES"]}[Content],

#"Navigation 2" = #"Navigation 1"{[Name = "MAPPINGS"]}[Content],

#"Navigation 3" = #"Navigation 2"{[Name = "Reporting_Dimensions.xlsx"]}[Content],

#"Imported Excel workbook" = Excel.Workbook(#"Navigation 3", null, true),

#"Navigation 4" = #"Imported Excel workbook"{[Item = "Stage_Order", Kind = "Table"]}[Data],

#"Changed column type" = Table.TransformColumnTypes(#"Navigation 4", {{"StageStatusPK", type text}, {"Order", Int64.Type}, {"IncomeAnaysisStage", type text}})

in

#"Changed column type"

Query Project_SD1:

let

Source = Excel.Workbook(Web.Contents("https://wearebulletproof.sharepoint.com/sites/ReportingLondon/Shared%20Documents/POWER%20BI%20WORKIN..."), null, true),

#"Navigation 1" = Source{[Item = "Project_SD", Kind = "Table"]}[Data],

#"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"Project ID", Int64.Type}, {"Project Name", type text}, {"Project Number", type text}, {"Project Status", type text}, {"Project Type", type text}, {"Brand Name", type text}, {"Region (for projections)", type text}, {"Client", type text}, {"Subsidiary Client", type text}, {"Project_Currency", type text}, {"ClientCode k ProjectNo", type text}, {"Project Client Group", type text}, {"ProCliGroup.Order", Int64.Type}, {"Subsidiary_Code_ClientName", type text}, {"Project_Location", type text}, {"Client - Brand", type text}, {"sub.Location", type text}, {"sub.symbol", type text}})

in

#"Changed column type"

Thanks in advance!
Beatriz Albuquerque

[GeraldGEmerick]

@beatrizalbuqu Are there any more details regarding the error?

[beatrizalbuqu]

Hi Gerald,

Thanks for replying so fast. No, that's all I get from the refresh history.

Last week I had a similar problem with another data flow that retrieved an error with Request ID and Activity ID. When I contacted Microsoft they said it was an authentication issue but my credentials were fine. In the end, I've found out the connection via SharePoint.Folder was the issue because it timed out and lost credentials before processing the data. So I changed it to fetch data via SharePoint.Contents instead and it worked. For the current Data Flow I'm having errors here, I'm already using SharePoint.Contents so I don't know what else could be the issue here.

[tayloramy]

Hi @beatrizalbuqu, 

Can you post the error message you are getting? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[beatrizalbuqu]

Error: Request ID: 313103cb-e96b-0357-13bb-894537de33c5 Activity ID: cb710714-a8a0-4d3f-8802-112c72ee8796

[v-veshwara-msft]

Hi @beatrizalbuqu ,

Thanks for reaching out to Microsoft Fabric Community and sharing the details.

Since other dataflows using the same SharePoint source are working and the error only shows a Request ID, it could be related to the size of the files or the complexity of transformations in this query.

You might check the file sizes in "POWER BI SOURCES" / MAPPINGS / SCREENDRAGON_DIMENSIONS", temporarily simplify or isolate some of the Expand or Merge steps, or try refreshing with a smaller number of rows to see if it succeeds.

Since the error provides limited information, raising a Microsoft support ticket is recommended so they can investigate the cause in the backend using the Request ID.

Hope this helps. Please reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @beatrizalbuqu ,

Just checking in to see if your query has been resolved and whether any of the responses were helpful. Otherwise, feel free to reach out for further assistance.

Additionally, could you please confirm whether the issue has been addressed through the support ticket with Microsoft?

If the issue is now resolved, we’d greatly appreciate it if you could share any key insights or the resolution here for the benefit of the wider community.

Thank you.

[beatrizalbuqu · Accepted solution]

Sorry, I appreciate you are trying to help but I didn't raise a ticket to Microsoft Support as I spent 2 days with them trying to get this error fixed for another data flow and they couldn't find which step of the query was causing the issue. They said that this issue falls outside the scope of their current support boundaries and that I should seek help from a dev. I ended up deleting the data flow and moving the query somewhere else as I couldn't afford spending more time investigating the error.

Microsoft could improve the error log for data flow refresh as this would save time for the users debug when something breaks i.e. displaying the item that caused the error rather than the back end ids.
I'll close this ticket and mark my last comment as solution but there was no solution whatsoever as I deleted the data flow.