Forum Discussion
Refresh From SharePoint Folder Works and Doesn't Work
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
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
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
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?