PowerQuery Sharepoint Table is empty error

[BJ]

Hi everyone,

I have an issue regarding a query that get and transform data from an excel in a sharepoint folder.

Every semester I create a new occurence of the file which has the query in it and change the path of the source.

Worked well for the last 2 semesters and now when i try to refresh the table is shown as "empty".

I tried clearing credentials, opening it from  a different computer, changing the path to local file instead of sharepoint without success. any clue ?

[Jimmy801]

Hello @BJ 

have you been able to solve the problem with the replies given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello @BJ 

without any information it's not possible to help. Maybe you have a filter somewhere that doesn't work for this semester (with months/dates calculated). Example could be that you use Date.Month = Date.Month - 3, witch could now result in a invalid month number and have no result anymore.

If you post the query that retrieves the data we can identify some issues. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[BJ]

Hello Jimmy

let
Source = SharePoint.Contents("https://stagsparticipations.sharepoint.com/sites/stags-participations/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Reporting Suivi Private Equity")),
#"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content.1", "Name.1", "Extension.1", "Date accessed.1", "Date modified.1", "Date created.1", "Attributes.1", "Folder Path.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Content", each ([Name.1] = "VL et engagements résiduels")),
#"Expanded Content.1" = Table.ExpandTableColumn(#"Filtered Rows1", "Content.1", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content", "Name.2", "Extension.2", "Date accessed.2", "Date modified.2", "Date created.2", "Attributes.2", "Folder Path.2"}),
#"CHANGE DATE" = Table.SelectRows(#"Expanded Content.1", each ([Name.2] = "2019.12.31 VL")),
#"Removed Columns" = Table.RemoveColumns(#"CHANGE DATE",{"Extension.2", "Date accessed.2", "Date modified.2", "Date created.2", "Folder Path.2", "Name.1", "Extension.1", "Date accessed.1", "Date modified.1", "Date created.1", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Folder Path.1", "Folder Path"}),
#"Expanded Content1" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content.1", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes.3", "Folder Path"}),
#"Filtered Rows3" = Table.SelectRows(#"Expanded Content1", each ([Name] = "Export Base")),
#"Expanded Content.2" = Table.ExpandTableColumn(#"Filtered Rows3", "Content.1", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content", "Name.1", "Extension.1", "Date accessed.1", "Date modified.1", "Date created.1", "Attributes.4", "Folder Path.1"}),
#"Filtered Rows4" = Table.SelectRows(#"Expanded Content.2", each Text.StartsWith([Name.1], "Operation")),

After that last line i should access the detail of my excel file but i end up with "This table is empty"

[Jimmy801]

Hello @BJ 

there are no real time filters applied, but a lot of other filters.

could you click on Refresh all and check every step to see how your data looks like after every step to identify witch filter hides all your data.

All the best

Jimmy

[BJ]

Thank you for your time,

as I said, table is empty occurs after the last line i copied

[Jimmy801]

Hello @BJ 

so I didn't get that

but then the simple thing is that there are no rows were in the Column Name.1 a value starts with "Operation".

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy