Wyczytywanie plików z folderu jest jedną z najlepszych rzeczy, jakie można znaleźć w Power Query w Excelu i po stronie Power BI. Część scenariuszy nie jest jednak taka prosta, ponieważ dane w plikach nie zawsze są gotowymi do wczytania tabelami. Oto 5 sposobów na problemy z wykorzystaniem formuł typu Table w języku M. Kto je obsłuży, zostaje okrzyknięty „królem Excela”. Temat omawiałem podczas #22 Data Viz & BI Meetup.

Problem 1: Brak wczytania nagłówków

To scenariusz, który zwykle pojawia się w plikach Excel. Po wczytaniu folderu z plikami Excel nagłówki z arkuszy pojawiają się w pierwszym wierszu.

Pomocna będzie formuła:

Table.PromoteHeaders([Data])

Formułę wstawiamy jako Dodaj kolumnę > Kolumna niestandardowa (1) > odwołujemy do kolumny z tabelą (2). Po kliknięciu na tabele w nowej kolumnie (3) możemy dostrzec nadane nagłówki (4). Dzięki temu mamy pewność, że operacja Dołącz, która jest integralną częścią procesu pobierania danych z folderu, wykona się na właściwych kolumnach.

Problem 2: Nagłówki nie są w pierwszym wierszu

Kolejnym wyzwaniem są dla nas dane, które zaczynają się zawsze w n-tym wierszu (np. w 5. wierszu).

Przyda się formuła:

Table.Skip([Data],4)

Dzięki niej możemy usunąć w każdym pliku n (w przykładzie 4) wierszy.

Pozostaje jeszcze poprawić nagłówki analogicznie, jak w scenariuszu nr 1.

Problem 3: Zmienna liczba niepotrzebnych wierszy

Jeśli liczba wierszy na początku tabeli różni się między plikami, możemy przesunąć dane w górę innym sposobem.

Użyjmy filtrowania wartościach pustych (null) w kolumnie, która zawiera tylko dane:

Table.SelectRows([Data], each ([Column16] <> null))

Ważne, aby wybrać „bezpieczną” kolumnę.

Problem 4: Zmienne nazwy kolumn

Jeśli nazwy kolumn się różnią, nie ma jednego uniwersalnego rozwiązania, a wiele zależy od tego, czy nazwa kolumny ma dla nas znaczenie. Jeśli nie, możemy wykonać dołączanie niezależnie od kolumn > odfiltrować niepotrzebne wiersze z nagłówkami > nadać nagłówki samodzielnie.

Jeśli nazwy kolumn mogą nam się przydać, możemy je przywołać do nowej kolumny poprzez Kolumnę warunkową.

A następnie wypełnić w dół.

Problem 5: Dane w postaci formularza

Jeśli dane nie mają układu tabeli, a są w postaci niezależnych komórek, czeka nas więcej pracy. Niemniej przyda się funkcja, która pozwala pobierać dane z konkretnych komórek Excel.

Oto funkcja do wykorzystania:

 

(tabela as table, kolumna as number, wiersz as number) =>

//definiowanie zmiennnych

let

DodajIndeks = Table.AddIndexColumn(tabela, "Indeks", 1, 1),

UsunwWiersze = Table.SelectRows(DodajIndeks, each ([Indeks] = wiersz)),

UsunKolumny = Table.SelectColumns(UsunwWiersze,{"Column"&Text.From(kolumna)})

in

UsunKolumny

 

Jak z niej skorzystać?

1. Skopiuj tekst funkcji.
2. Utwórz nowe puste zapytanie.

3. Otwórz Edytor zaawansowany i wklej formułę.

4. Wywołaj funkcję niestandardową, wskazując funkcję, kolumnę z tabelami (u mnie Data), kolumnę i wiersz szukanej komórki.

5. Pamiętaj, aby po rozpakowaniu, nazwać właściwie Twoje dane.

Pliki do pobrania

Oto komplet plików do pobierania danych z folderu. Aby działały zapytania, podłącz je do folderów na Twoim komputerze w kroku Źródło x 2 (zapytanie główne z folderu Inne zapytania i zapytanie o nazwie Przykładowy plik).