Refresh From SharePoint Folder Works and Doesn't Work

[UncleLewis]

Hi all,

Using PBID Nov, 2024.
I create a connection to a SharePoint Folder got everything working created a table everything looks good

Published to a workspace on my tenant.
Configured the SharePoint Connection so I can refresh
Creeated a new file with sample values and added to the SP Folder
Tried to refresh - bupkis - no error mesages - but also no data from the 4th file in the folder.

Opened the pbix - hit refresh - success! Data from all 4 files.

Any thoughts why the refresh in the pbix is retrning data as expected but when I refresh the Model in the Service it does not return the data for the 4th file?

Thanks,
w

[UncleLewis · Accepted solution]

@lbendlin,

I wonder if there was something wrong with the Power BI Service earlier.
I just checked the report and it was showing values from the 4th file added.
I tested again adding a 5th file.
I refreshed the Model in the Service and the data for the 5th files is showing in the report.

Appreciate your help.

Thanks,

w

[UncleLewis]

Thanks @lbendlin ,

Transform Sample File

let
Source = Excel.Workbook(Parameter1, null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"

SharePointFolder

let
Source = SharePoint.Contents("https://fakename.sharepoint.com/sites/fake", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
Data = #"Shared Documents"{[Name="Data"]}[Content]
in
Data

Consolidation

let
Source = SharePointFolder,
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Month", type text}, {"Color", type text}, {"Units", Int64.Type}})
in
#"Changed Type"

Thanks,

w

[lbendlin]

You have used the "Combine Binaries"  shortcut.  Nothing inherently wrong with that, just slightly inefficient.  You get better results with inlining the transforms.

Your data sources are Excel files - you will want to make sure that your newly added files are not open in any client app during the refresh.  Would be better if you could use CSV files.

Does the issue persist when you try the service refresh again?

[UncleLewis · Accepted solution]

@lbendlin,

I wonder if there was something wrong with the Power BI Service earlier.
I just checked the report and it was showing values from the 4th file added.
I tested again adding a 5th file.
I refreshed the Model in the Service and the data for the 5th files is showing in the report.

Appreciate your help.

Thanks,

w

[Anonymous · Accepted solution]

Hi,@UncleLewis 
We are delighted that you have found a solution and are willing to share it.

Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

Best Regards,

Leroy Lu

[lbendlin]

You seem to be following the correct steps. Can you maybe post a sanitized version of the Power Query code?