Dataflow: Traceback Invalid Cell Value Errors to Source

[PowerBI_Chaos]

Hello all,

Needing some guidance! Hopefully there is a simple solution available!

I am working through several power BI report dataflows (through Fabric) which have errors relating to Pipeline Exceptions, Invalid Cell Value. However, when I go into the power query none of the steps are erroring out due to this openly, only the Pipeline is. However, I do see the error arise if I try to open the filters on the query and load more.

Typically, I would simply open the datasource and CTRL + F for the erroneous value...

The trouble is that the report is using a custom function to pull data from a folder full of templated spreadsheets into one consolidated view. I cannot tell which workbook the data error is stemming from.

My question is: Is there a method in Power BI to quickly and easily identify the source of the errors in the Excel workbooks they are pulling from?

Here is the error, as seen from Fabric dataflow refresh:

Error: DataFormat.Error: Invalid cell value '#REF!'. . RootActivityId = efe8610d-fb49-442c-ace3-09d4f9d8a6af.Param1 = DataFormat.Error: Invalid cell value '#REF!'. Request ID: 19fda3b9-bf43-485c-af05-11b147f97b8f.

[PowerBI_Chaos · Accepted solution]

I was able to resolve this issue by inserting a temporary step after the folder of excel workbooks is loaded into the query, which filtered the workbooks down by name into batches (beginning with).  Using this I was able to test batches of about 5-10 at a time, until I narrowed it down to the two files which were throwing the "#REF!" errors.  I opened them up, cleared up the formulas generating the errors, and now the dataflow is working again.  

[BA_Pete]

Hi @PowerBI_Chaos ,

#REF! and #N/A are Excel errors, not Power Query errors, so you're correct to want to trace this back to the Excel source.

To get this identified quickly you need to first: ignore generic ChatGPT answers, second; do it in Excel (not in the Dataflow). Dataflows are slow to load/filter and the rows aren't guaranteed to be in the same order as your source anyway.

In your Excel file you can either:

-1- Hit F5 (Go To), click on 'Special', select 'Formulas' radio button, and check the 'Errors' box.

-2- Hit Ctrl+F, type #REF! or #N/A in the Find field and either 'Find All' or 'Find Next'.

Pete

[PowerBI_Chaos]

Hi there,

Thank you for the reply.  I had tried to adjust my post last night, but it appears to have not went through.  While I was able to resolve one of the two, using the exact method mentioned... The problem is that the second power query pulls data from a bunch of excel files in a folder. Is there any way in Power Query to tell which of the files the errors are coming from?  Otherwise I would need to open each and every one of them up, and there are like 50 or more.

I took this over from my predecessor, and am trying to piece it back together.  Pulling data in this manner is not my preferred method.

[BA_Pete]

When you say that "the second power query pulls data from a bunch of excel files in a folder", do you mean it uses the Transform & Combine folder connector? You can tell if this is the case as you will have an auto-generated folder of queries called "Helper Queries" or similar.

If so, then you should see a [Source.Name] column in the combined query (the final one where all the files get combined together, not a helper query) that will tell you exactly which file each row has come from. You may need to click back up the query step list a bit in case this column has been removed in subsequent steps but, once you find it, you can use it identify exactly where your errors have come from.

Pete

[PowerBI_Chaos]

Thank you for your reply BA.Pete.

The query pulls yearly financial data from a folder full of excel documents using a custom function it seems.  Below is the function (step) that is used to pull in this data.  All of the preceding steps are getting to the folder location and removing unncessary files through transformations.  After this step it is normal transformations cleaning up the data. Nothing appears wrong within the transformations, however when/if I try and 'load more' than the sample (1000) rows then the error data.format error '#REF!' appears.  The problem is that the query does not tell me where the error is stemming from, which of the myriad of excel files that the query pulls from within the folder.  

-------------------------------------------------------------------

(FileName as text, FolderPath as text)=>

let

    Source = SharePoint.Files(

        "https://sitename.sharepoint.com/sites/", [ApiVersion = 15]

        ),

    #"Selected File" = Source{

        [Name=FileName,

        #"Folder Path"=FolderPath

        ]}[Content],

    #"Imported Excel" = Excel.Workbook(

        #"Selected File"

        ),

    #"Selected Range" = #"Imported Excel"{

        [Item="R_RollupData",Kind="DefinedName"]

        }[Data],

  #"Promoted Headers" = Table.PromoteHeaders(#"Selected Range", [PromoteAllScalars = true]),

  #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers", {

    "F19-01 Hours", "F19-02 Hours", "F19-03 Hours", "F19-04 Hours", "F19-05 Hours", "F19-06 Hours", "F19-07 Hours", "F19-08 Hours", "F19-09 Hours", "F19-10 Hours", "F19-11 Hours", "F19-12 Hours",

    "F20-01 Hours", "F20-02 Hours", "F20-03 Hours", "F20-04 Hours", "F20-05 Hours", "F20-06 Hours", "F20-07 Hours", "F20-08 Hours", "F20-09 Hours", "F20-10 Hours", "F20-11 Hours", "F20-12 Hours",

    "F21-01 Hours", "F21-02 Hours", "F21-03 Hours", "F21-04 Hours", "F21-05 Hours", "F21-06 Hours", "F21-07 Hours", "F21-08 Hours", "F21-09 Hours", "F21-10 Hours", "F21-11 Hours", "F21-12 Hours",

    "F22-01 Hours", "F22-02 Hours", "F22-03 Hours", "F22-04 Hours", "F22-05 Hours", "F22-06 Hours", "F22-07 Hours", "F22-08 Hours", "F22-09 Hours", "F22-10 Hours", "F22-11 Hours", "F22-12 Hours",

    "F23-01 Hours", "F23-02 Hours", "F23-03 Hours", "F23-04 Hours", "F23-05 Hours", "F23-06 Hours", "F23-07 Hours", "F23-08 Hours", "F23-09 Hours", "F23-10 Hours", "F23-11 Hours", "F23-12 Hours",

    "F24-01 Hours", "F24-02 Hours", "F24-03 Hours", "F24-04 Hours", "F24-05 Hours", "F24-06 Hours", "F24-07 Hours", "F24-08 Hours", "F24-09 Hours", "F24-10 Hours", "F24-11 Hours", "F24-12 Hours",

    "F25-01 Hours", "F25-02 Hours", "F25-03 Hours", "F25-04 Hours", "F25-05 Hours", "F25-06 Hours", "F25-07 Hours", "F25-08 Hours", "F25-09 Hours", "F25-10 Hours", "F25-11 Hours", "F25-12 Hours",

    "Team", "Role", "Name", "Rate", "Specialty", "Timesheet Activity"}),

  #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Project Number", "Spend Type", "Source", "Vendor Name", "Quote Number", "Description", "Spend Class", "Spend Category", "Table Row", "File Template Version", "File Last Refresh CT"}, "FYY-FP", "Cost"),

  #"Filtered Rows - Cost <> 0" = Table.SelectRows(#"Unpivoted Columns", each [Cost] <> 0 and [Cost] <> null and [Cost] <> "0" and [Cost] <> ""),

  #"Added Column - Fiscal Year" = Table.AddColumn(#"Filtered Rows - Cost <> 0", "Fiscal Year", each Number.From(

            "20" & Text.Middle(

                [#"FYY-FP"],1,2)

                )),

  #"Added Column - Fiscal Period" = Table.AddColumn(#"Added Column - Fiscal Year", "Fiscal Period", each Number.From(Text.End([#"FYY-FP"], 2))),

  #"Renamed Columns" = Table.RenameColumns(#"Added Column - Fiscal Period", {{"Cost", "Amount"}, {"Spend Type", "Transaction Type"}, {"Project Number", "Project Number Short"}}),

  #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns", {"FYY-FP"}),

  #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1", {{"Project Number Short", Int64.Type}, {"Fiscal Year", Int64.Type}, {"Fiscal Period", Int64.Type}, {"Table Row", Int64.Type}, {"Amount", Currency.Type}, {"File Last Refresh CT", type datetime}, {"Spend Category", type text}, {"Spend Class", type text}, {"Description", type text}, {"Quote Number", type text}, {"Vendor Name", type text}, {"Source", type text}, {"Transaction Type", type text}})

in

  #"Changed Type"

[BA_Pete]

Ok. If you were using the standard folder connector you would be able to select the step where the custom function is invoked and see which nested table showed as an error.

As it is, I think you'll need to go into your Dataflow, select the combined query, hit Ctrl+A on the table to select the whole thing, then go to the Home tab > Keep Rows > Keep Errors.

Depending how large/complex your data pipeline is, this could take a significant amount of time, but it should retain every row that contains an error in any column which you can hopefully use to identify the offending file based on the data contained in the other columns.

Pete

[123abc]

Identifying the exact workbook, sheet, row/column, and cell causing errors in Power BI dataflows can be a bit challenging, but it is possible with a systematic approach. Here are some steps you can follow to pinpoint the source of the errors:

1. Check Data Sources:

   • First, verify the data sources you are pulling from. Ensure that the data is well-structured and does not contain any issues like #N/A or #REF! errors. Sometimes these errors originate in the source data.

2. Dataflow Query Errors:

   • In Power BI, when you load data into a dataflow, the Power Query Editor performs transformations on the data. Check the query editor for each dataflow. Look for any steps that might introduce errors like dividing by zero, referencing invalid cells, or handling missing values incorrectly. Fix any such issues within the Power Query Editor.

3. Column Profiling:

   • Use the "Column Profiling" feature in Power Query Editor to analyze the data and identify anomalies. This can help you identify which columns might be causing issues. To use this feature, select a column and go to the "View" tab in Power Query Editor, then choose "Column Profiling."

4. Data Lineage:

   • In Power BI, data lineage can help you understand where data is coming from and where it's going. Use the "Data lineage view" to trace back to the data source and identify the problematic cell in Excel. This view might show you the path from the source data to the error in the pipeline.

5. Error Handling in Power Query:

   • In Power Query, you can implement custom error handling to capture and record problematic cells. For example, you can add custom columns that flag rows containing errors or invalid values. This can make it easier to identify the source.

6. Testing Individual Steps:

   • You mentioned that none of the steps in Power Query are showing errors. It's possible that the issues are arising during the merge or transformation steps. You may need to isolate and test individual steps to identify which one introduces the error.

7. Excel Workbook Audit:

   • If the issue persists, you might have to audit the Excel workbooks manually. Open the Excel files used as data sources, and look for the specific cells or formulas that contain #N/A or #REF!. Once you identify these cells, you can decide how to handle them in your Power Query transformations.

8. Data Validation:

   • Implement data validation in your Excel workbooks. This can help prevent invalid values from getting into your dataflow in the first place. You can use Excel's data validation features to restrict the input to acceptable values.

9. Error Logging:

   • Consider implementing error logging within Power Query or in the data loading process to track and capture errors as they occur. This can help you pinpoint the exact location of problematic data.

10. Consult Power BI Community:

    • If you are still facing difficulties, consider posting your issue on the Power BI community forums. Experienced Power BI users might be able to provide specific advice based on your report and dataflow structure.

Identifying the exact source of these errors may require a combination of these methods, as well as careful examination of both the Power Query steps and the source Excel files.