Łączenie plików z SharePoint działa bardzo wolno - SharePoint.Files czy SharePoint.Contents

Power Query w Excelu, Power BI i Fabric

Łączenie plików z SharePoint działa bardzo wolno - SharePoint.Files czy SharePoint.Contents

W skrócie

Power Query wolno łączy pliki z SharePoint między innymi wtedy, gdy raport korzysta z małego folderu na dużej witrynie zespołu albo w OneDrive dla firm. Pokazujemy, skąd bierze się czas odświeżania, czym różnią się funkcje SharePoint.Files i SharePoint.Contents oraz jak przepiąć zapytanie na nawigację prosto do folderu. Punktem wyjścia jest połączenie z lekcji o Dataflow Gen2, w którym miesięczne eksporty Nordvelli leżą w OneDrive.

Jak to wygląda w praktyce

Po wpisaniu adresu witryny w łączniku Folder programu SharePoint (w Excelu Z folderu programu SharePoint) okno podglądu pokazuje pliki z całej witryny i ze wszystkich podfolderów, także te, które z raportem nie mają nic wspólnego. W lekcji o Fabric łącznik zwrócił listę plików z całego OneDrive, choć potrzebowaliśmy czterech plików z jednego folderu. Każde odświeżenie zaczyna się od pobrania tej listy, więc trwa tym dłużej, im więcej dokumentów jest w witrynie, nawet jeśli łączysz tylko kilka małych plików CSV.

W Power Query Online widać to we wskaźnikach składania zapytań, czyli informacji, czy krok wykona źródło danych. Przy kroku filtra po ścieżce folderu podpowiedź brzmi: Ten krok zostanie oceniony poza źródłem danych. Filtr nie skraca więc pobierania listy, tylko odrzuca zbędne pozycje po jej pobraniu.

Dlaczego tak się dzieje

Opis funkcji w Power Query jest jednoznaczny. SharePoint.Files zwraca tabelę z wierszem dla każdego dokumentu znalezionego w podanej witrynie oraz podfolderach. SharePoint.Contents zwraca wiersz dla każdego folderu i dokumentu, przy czym folder ma w kolumnie Content tabelę z dalszą zawartością, więc do celu dochodzisz poziom po poziomie. Dokumentacja Microsoft dodaje, że nawigacja przez SharePoint.Contents sprawdza się najlepiej w witrynach SharePoint i OneDrive dla firm z dużą liczbą plików.

W naszym przepływie łącznik wygenerował SharePoint.Files z adresem całego OneDrive, a dopiero następny krok zawęża listę: Table.SelectRows(Źródło, each Text.Contains([Folder Path], "Sprzedaz_miesieczna")). Te same kroki powtarza zapytanie Przykładowy plik, bo ma własną kopię listy plików. Druga sprawa to opcja ApiVersion: bez niej Power Query używa wersji 14 interfejsu SharePoint, a według opisu opcji witryny w języku innym niż angielski wymagają co najmniej wersji 15.

Jak to rozwiązać krok po kroku

1. Sprawdź, czym czytasz pliki: otwórz zapytanie główne, kliknij krok Źródło i spójrz na pasek formuły. Jeśli widzisz SharePoint.Files, a za nim filtr po kolumnie Folder Path, zapytanie pobiera listę całej witryny przy każdym odświeżeniu.
2. Utwórz nowe zapytanie z nawigacją do folderu: w Excelu wybierz Dane, Pobierz dane, Z innych źródeł, Puste zapytanie i w pasku formuły wpisz = SharePoint.Contents("https://twojafirma.sharepoint.com/sites/Kontroling", [ApiVersion = 15]). Adres to adres witryny, a dla OneDrive dla firm adres w postaci https://twojafirma-my.sharepoint.com/personal/twoje_konto. Przy pierwszym połączeniu wybierz uwierzytelnianie Konto organizacyjne.
3. W wyniku znajdź wiersz biblioteki dokumentów i kliknij wartość Table w kolumnie Content. Tak samo przejdź do folderu z eksportami, na przykład Sprzedaz_miesieczna. Każde kliknięcie dodaje krok nawigacji, a lista zawiera już tylko pliki i podfoldery tego jednego miejsca.
4. Kliknij ikonę z dwiema strzałkami w nagłówku kolumny Content. Otworzy się to samo okno Połącz pliki co przy folderze lokalnym, z ustawieniami Pochodzenie pliku, Ogranicznik i Wykrywanie typu danych. Kroki czyszczenia budujesz potem w zapytaniu Przekształć przykładowy plik, jak w lekcji o łączeniu plików.
5. Jeśli wolisz zachować istniejące zapytania, podmień w nich początek. W zapytaniu głównym i w zapytaniu Przykładowy plik otwórz Edytor zaawansowany, zastąp krok SharePoint.Files i filtr ścieżki krokami nawigacji z poprzednich punktów, a kolejny krok skieruj na ostatni z nich. Wzorzec do dostosowania: Biblioteka = Źródło{[Name = "nazwa biblioteki"]}[Content], potem Folder = Biblioteka{[Name = "Sprzedaz_miesieczna"]}[Content]. Nazwy przepisz z kolumny Name.
6. Gdy zostajesz przy SharePoint.Files, ogranicz koszt: dopisz [ApiVersion = 15], filtr po Folder Path ustaw bezpośrednio za krokiem Źródło i podawaj adres najmniejszej witryny, która zawiera Twoje pliki. Najpewniej działa osobna biblioteka albo witryna tylko na eksporty do raportu.

Jak sprawdzić, że zadziałało

Po zmianie krok nawigacji powinien pokazywać tylko pliki z wybranego folderu, a kolumna Source.Name w zapytaniu głównym tylko eksporty, które mają trafić do raportu. Zmierz czas odświeżenia przed zmianą i po niej na tych samych danych: w Excelu przyciskiem Odśwież wszystko na karcie Dane, w Dataflow Gen2 w oknie Ostatnie uruchomienia, które pokazuje czas trwania każdego przebiegu. Porównaj też liczbę wierszy wyniku z poprzednią wersją zapytania.

Pamiętaj o różnicy w zakresie: SharePoint.Contents nie wciąga plików z podfolderów, dopóki do nich nie przejdziesz. Jeśli eksporty trafiają do podfolderów według roku, trzymaj je w jednym folderze albo przejdź do każdego z nich. Żeby problem nie wrócił, nie odkładaj w folderze raportu plików roboczych i kopii, a nowe raporty podłączaj od razu przez nawigację do folderu.

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5