Forum Discussion

PowerBI_Chaos's avatar
PowerBI_Chaos
Helper I
2 years ago
Solved

Dataflow: Traceback Invalid Cell Value Errors to Source

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 Pipelin...
Need Help
  • PowerBI_Chaos's avatar
    PowerBI_Chaos
    2 years ago

    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's avatar
BA_Pete
Super User
2 years ago

 

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's avatar
PowerBI_Chaos
Helper I
2 years ago

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's avatar
    BA_Pete
    Super User
    2 years ago

     

    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