Forum Discussion
PowerBI Service Refresh duration
Hi,
i have a report based on excelfiles in a sharepointfolder.
The Refresh Duration in Service is very long. If i refresh the data in Desktop it has only 30% of that duration.
How can i optimize it?
Regards
Ok. There are two things you can try here. If neither of them works, you can use dataflows to run the process in a different cloud environment. Both approaches are not dependant, you can try one, another or both.
1- Try changing the way it takes the source. I usually connect sharepoint by conent and not by "files" when I want to combine. You can check how to do it in the following post: https://blog.ladataweb.com.ar/post/627427136962281472/powerbi-obtener-datos-de-una-carpeta-de
2- Change the way you combine the files. You can change the way the tables are combine to avoid the creation of samples and stuff like curbal has shown in the following two posts:
Part1: https://www.youtube.com/watch?v=hJF2dPfIeaM
Part2: https://www.youtube.com/watch?v=UY3hIV-THcg
I hope one of those helps,
3 Replies
Hi! can you share the way you are connecting to sharepoint with Power Query? is it a single file or a combination of files?
I have heard about some issues with sharepoint, my mates have moved the Power Query to Dataflows to make it work faster. Then connecting datasets to the dataflow.
I hope that helps,
Hi,
i connected to sharepoint folder and filtered out unneccesary files and then combine.
Quelle = SharePoint.Files("https://----.sharepoint.com/teams/Name", [ApiVersion = 15]),
#"Gefilterte Zeilen" = Table.SelectRows(Quelle, each ([Folder Path] = "https://----.sharepoint.com/teams/Data/") and ([Extension] = ".xlsx")),
#"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(#"Gefilterte Zeilen", each [Attributes]?[Hidden]? <> true),
Benutzerdefiniert1 = #"Gefilterte ausgeblendete Dateien1",
#"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(Benutzerdefiniert1, "Datei transformieren", each #"Datei transformieren"([Content])),
#"Umbenannte Spalten1" = Table.RenameColumns(#"Benutzerdefinierte Funktion aufrufen1", {"Name", "Source.Name"}),
#"Andere entfernte Spalten1" = Table.SelectColumns(#"Umbenannte Spalten1", {"Source.Name", "Datei transformieren"}),
#"Gefilterte Zeilen1" = Table.SelectRows(#"Andere entfernte Spalten1", each ([Source.Name] <> "Category.xlsx" and [Source.Name] <> "Queues.xlsx"))Afterwards i correct manually the variant datatype to the right one in power query.
Regards
Ok. There are two things you can try here. If neither of them works, you can use dataflows to run the process in a different cloud environment. Both approaches are not dependant, you can try one, another or both.
1- Try changing the way it takes the source. I usually connect sharepoint by conent and not by "files" when I want to combine. You can check how to do it in the following post: https://blog.ladataweb.com.ar/post/627427136962281472/powerbi-obtener-datos-de-una-carpeta-de
2- Change the way you combine the files. You can change the way the tables are combine to avoid the creation of samples and stuff like curbal has shown in the following two posts:
Part1: https://www.youtube.com/watch?v=hJF2dPfIeaM
Part2: https://www.youtube.com/watch?v=UY3hIV-THcg
I hope one of those helps,