Power BI – pobieranie danych z wielu arkuszy Excela

Witam Was po krótkiej wakacyjnej przerwie, dziś wracamy do tematu Power BI i powiemy sobie jak w prosty sposób załadować dane z wielu arkuszy Excela – zapraszam do lektury.

Jakiś czas temu pokazywałem prostą metodę ładowania wielu plików z określonej lokalizacji (link), dziś temat kontynuujemy i pokażemy jak sobie poradzić w sytuacji gdy musimy załadować dane z arkuszy wewnątrz pojedynczego pliku Excel. Mój zestaw testowy składa się z trzech arkuszy będących ekstraktem bazy AdventureWorksDW2017, a raczej zawartej w niej tabeli FactInternetSales:

Jak łatwo zauważyć na powyższym zrzucie ekranowym każda zakładka zawiera dane z innego roku. Spróbujmy załadować te dane do Power BI wybierając standardowy konektor Excela:

Jego konfiguracja raczej nikomu nie powinna przysporzyć problemów i sprowadza się do podania ścieżki do interesującego nas Excela. Po wskazaniu pliku dostaniemy listę obiektów z danymi, które możemy załadować. Wylistowane zostały poszczególne arkusze oraz tabele(nazwane zakresy) które akurat w swoim pliku Excel posiadam. Kliknijmy prawym przyciskiem myszy na główny węzeł i z menu kontekstowego wybierzmy Transform Data aby przejść do edytora zapytań:

W edytorze zapytań wybieramy tylko te kolumny, które nas interesują czyli “Name” oraz “Data” – resztę możemy usunąć:

Wiersze z poszczególnymi latami symbolizują arkusze, wiersze zaczynające się od “SalesData” symbolizują tabele excelowe – nas będą interesować arkusze czyli odfiltrowujemy pozostałe wiersze według następujących zasad:

Następnie rozszerzamy kolumnę “Data” tak aby załadować dane z poszczególnych arkuszy:

Po załadowaniu dane wyglądają następująco:

Wygląda to całkiem dobrze ale mamy tutaj dwa problemy do rozwiązania których przejdziemy za chwilę. Pierwszą rzeczą jaką chcemy zrobić jest przeniesienie pierwszego wiersza do nagłówka:

Mimo, że ta operacja wydaje się, że powinna rozwiązać większość naszych problemów to nie wszystko jednak jest już gotowe. Wystarczy spojrzeć na poniższy zrzut ekranowy:

Wybrałem kolumnę, która nie ma zbyt wielu wartości unikalnych i możemy zauważyć, że zawiera ona m.in. wartość nagłówkową.. Wygląda to tak, że dane znajdujące się na każdym arkuszu zostały załadowane wraz z nagłówkami tak jakby były to normalne wartości. A to jeszcze nie koniec! Kolejnym problemem będzie nazwa samego arkusza, która zawiera rok. Po przeniesieniu danych do nagłówka rok również tam trafi:

Co w tym takiego problematycznego? Problem może pojawić się przy zmianie nazwy tej kolumny na “Year” ponieważ wygenerowany kod wygląda następująco:

= Table.RenameColumns(#"Changed Type",{{"2011", "Year"}})

Będzie to w porządku w przypadku gdy ładowany Excel zawsze będzie zawierał arkusz o nazwie 2011… W przeciwnym razie dostaniemy błąd, że taki konkretny arkusz nie istnieje i próba zmiany nazwy się nie powiedzie. Ponadto jak przejrzymy wygenerowany kod to zobaczymy zaszyte na sztywno odwołania już w poprzednim kroku:

Jak więc sobie poradzić z tym bałaganem?Jedno z rozwiązań zaproponował Matt Allington w swoim video (link) gdzie użył funkcji. My jednak rozwiążemy ten problem w nieco prostszy sposób. Aby tego dokonać najłatwiej cofnąć się do pierwszego kroku gdzie łączymy się do Excela:

Pod tym krokiem kryje się zagnieżdżone wywołanie dwóch funkcji Excel.Workbook oraz File.Contents:

= Excel.Workbook(File.Contents("C:\Users\adria\Desktop\Sales.xlsx"), null, true)

Jedyne co musimy zrobić to zmodyfikować pierwszą z nich tak aby jej drugi parametr nie przyjmował wartości null tylko True. Czym jest ten parametr? Jest to wskazanie czy pierwsze wiersze mają być traktowane jako nagłówki czy też nie. Ta prosta zmiana powinna skutkować następującym kodem w pierwszym kroku naszego zapytania:

= Excel.Workbook(File.Contents("C:\Users\adria\Desktop\Sales.xlsx"), true, true)

Zmiana ta spowoduje, że będziemy musieli od nowa przeklikać krok związany z rozszerzeniem tabeli i użyciem pierwszego wiersza jako nagłówka najlepiej kliknąć prawym przyciskiem myszy na krok odpowiadający za rozszerzenie i z menu kontekstowego wybrać Delete Until End:

Następnie musimy od nowa dane rozszerzyć i wtedy nagłówki automatycznie trafią na swoje miejsce bez konieczności użycia transformacji Use First Rows As Headers:

Pierwszy wiersz każdego z arkuszy jest teraz traktowany jako nagłówek i nie zostanie załadowany jako wartość co można w łatwy sposób sprawdzić na wybranej wcześniej kolumnie:

Voila! Problem rozwiązany bez większego wysiłku. Cały kod zamknął się czterech krokach:

let
    Source = Excel.Workbook(File.Contents("C:\Users\adria\Desktop\Sales.xlsx"), true, true),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Data", "Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each not Text.StartsWith([Name], "SalesData") and not Text.StartsWith([Name], "ExternalData")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"SalesOrderID", "SalesOrderNumber", "CustomerID", "TerritoryID", "Comment", "ProductID", "UnitPrice", "UnitPriceDiscount", "LineTotal", "OrderQty", "OrderDate", "DueDate", "ShipMethodID"}, {"SalesOrderID", "SalesOrderNumber", "CustomerID", "TerritoryID", "Comment", "ProductID", "UnitPrice", "UnitPriceDiscount", "LineTotal", "OrderQty", "OrderDate", "DueDate", "ShipMethodID"})
in
    #"Expanded Data"

Nigdzie nie mamy zapisanego na sztywno odwołania do nazwy arkusza tak więc Power BI odświeży się bez większego problemu nawet w przypadku dodania nowych arkuszy czy też zmiany ich nazw. Ogólnie polecam jeśli to tylko możliwe tworzyć w Excelu tabele bo Power BI znacznie lepiej sobie z nimi radzi i powyższy problem w takim scenariuszu nie występuje. Często natomiast spotyka się przypadki generowania Exceli przez zewnętrzne oprogramowanie gdzie zazwyczaj dane przechowywane są w zwykłym zakresie danych.

Mam nadzieję, że ta prosta wskazówka ułatwi Wam ładowanie tego typu struktur excelowych i unikniecie bolesnego pisania obejść i tzw. workaroundów. Pozdrawiam i życzę miłego dnia!