Forum Discussion

MH_Ger's avatar
MH_Ger
Frequent Visitor
4 years ago

Error but nor error when refreshing Sharepoint connection

 

Hi everybody!

 

I am experiencing a weird error that I can neither explain nor get rid of. I am connecting to a Sharepoint folder and append a couple of files. This works wonderfully. The issue is that, everytime I refresh my connections, I get the following error:

 

 

The good news is that everything works perfectly despite the error message. All records are imported, all numbers add up.

The bad news is that this will scare other users. Hence it needs to go away.

 

What is weird about this error that no errors actually exists. There is no column in Power Query that contains the value "Error".

Consequently, the "Table.SelectRowsWithErrors" function yields an empty table (see picture below).

 

 

This is the code for the sharepoint folder connection:

 

 

 

let
    Source = SharePoint.Files("[Url removed]", [ApiVersion = 15]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes"})
in
    #"Removed Columns"

 

 

 

And this is the code for the error query:

 

 

 

let
Source = #"SharePoint Folder",
  #"Detected Type Mismatches" = let
    tableWithOnlyPrimitiveTypes = Table.SelectColumns(Source, Table.ColumnsOfType(Source, {type nullable number, type nullable text, type nullable logical, type nullable date, type nullable datetime, type nullable datetimezone, type nullable time, type nullable duration})),
    recordTypeFields = Type.RecordFields(Type.TableRow(Value.Type(tableWithOnlyPrimitiveTypes))),
    fieldNames = Record.FieldNames(recordTypeFields),
    fieldTypes = List.Transform(Record.ToList(recordTypeFields), each [Type]),
    pairs = List.Transform(List.Positions(fieldNames), (i) => {fieldNames{i}, (v) => if v = null or Value.Is(v, fieldTypes{i}) then v else error [Message = "The type of the value does not match the type of the column.", Detail = v], fieldTypes{i}})
in
    Table.TransformColumns(Source, pairs),
  #"Added Index" = Table.AddIndexColumn(#"Detected Type Mismatches", "Row Number" ,1),
  #"Kept Errors" = Table.SelectRowsWithErrors(#"Added Index", {"Content", "Name", "Folder Path"}),
  #"Reordered Columns" = Table.ReorderColumns(#"Kept Errors", {"Row Number", "Content", "Name", "Folder Path"})
in
  #"Reordered Columns"

 

 

 

Does anyone have any idea?

4 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion

    Hi MH_Ger ,

     

    Try to check the Date fileds. Sometimes that is what causing the error.

    If you want to see the error, on the lower left of the power query, double click this

    and then Replace the errors with null and try again.

     

    Hope this helps.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    MH_Ger 

     

    Go to Data Source Settings  -> Edit Permissions -> Re-enter credentials and log in. I would also suggest you to check that the account used to connect to the SharePoint site has proper permissions.

     

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

  • MH_Ger's avatar
    MH_Ger
    Frequent Visitor

    Hi! Sadly, none of your tips really helped. However, I was able to solve it myself. I replaced "SharePoint.Files" with "SharePoint.Contents" when loading all data into Power BI. I then simply navigated to my folder and voila: No errors when refreshing! 

  • I'm getting the same error - 'detected type mismatches', but no rows showing errors.