Dane się dublują przy łączeniu arkuszy jednego skoroszytu - arkusz i tabela naraz

Power Query w Excelu, Power BI i Fabric

Dane się dublują przy łączeniu arkuszy jednego skoroszytu - arkusz i tabela naraz

W skrócie

Duplikaty przy łączeniu arkuszy w Power Query pojawiają się w skoroszytach, w których każdy miesiąc albo oddział ma swój arkusz, a dane na arkuszach sformatowano jako tabele. Pokazujemy, jak wygląda lista obiektów skoroszytu, dlaczego te same wiersze trafiają do wyniku dwa razy i jak ustawić filtr, który obejmie też arkusze dodane w przyszłości. Wyniki sprawdziliśmy w Excelu na skoroszycie z dwoma arkuszami miesięcznymi, ukrytym arkuszem słowników i nazwanym zakresem.

Jak to wygląda w praktyce

Łączysz arkusze tak jak w lekcji o pierwszych krokach: w oknie Nawigator zaznaczasz nazwę całego pliku zamiast pojedynczego arkusza, klikasz Przekształć dane i rozwijasz kolumnę Data. Wynik ma więcej wierszy, niż jest danych. W naszym skoroszycie arkusze Styczeń i Luty zawierają razem 5 wierszy sprzedaży, a po rozwinięciu wszystkich obiektów, z pierwszym wierszem arkuszy użytym jako nagłówki, wyszło 14 wierszy. Wiersz z kodem produktu BEX-1021 występuje dwa razy, a w kolumnie Sklep pojawiają się dodatkowe pozycje z ukrytego arkusza Słowniki i z nazwanego zakresu.

Gdy arkusze nie mają promowanych nagłówków, obraz jest inny, ale równie mylący. Wiersze z nazwami kolumn (Sklep, Kod produktu, Ilość) stoją w danych, a kolumny się rozjeżdżają: wiersze z arkuszy mają wartości w kolumnach Column1, Column2, Column3, wiersze z tabel w kolumnach Sklep, Kod produktu, Ilość. Takiego wyniku nie naprawi Usuń duplikaty: usunie także prawdziwe, identyczne wiersze, a wiersze ze słowników zostaną.

Dlaczego tak się dzieje

Funkcja Excel.Workbook, którą Power Query czyta skoroszyt, zwraca tabelę obiektów z kolumnami Name, Data, Item, Kind i Hidden. Kolumna Kind mówi, czym jest obiekt: Sheet to arkusz, Table to tabela Excela, DefinedName to nazwany zakres. Te wartości są po angielsku także w polskim Excelu. Tabela tStyczen leży na arkuszu Styczeń, więc te same komórki są na liście dwa razy: jako obiekt Table i jako część obiektu Sheet. W naszym skoroszycie lista miała sześć pozycji: trzy arkusze (w tym Słowniki z wartością true w kolumnie Hidden), dwie tabele i jeden nazwany zakres.

Obiekty różnią się też budową. Tabela ma nagłówki ze swojej definicji, a arkusz domyślnie ma kolumny Column1, Column2 i wiersz nagłówka w danych. Drugi argument funkcji ustawiony na true każe traktować pierwszy wiersz każdego arkusza jako nagłówek:

Excel.Workbook(File.Contents("C:\Dane\Nordvella\Sprzedaz_2026.xlsx"), true, true)

Wtedy arkusze i tabele mają te same kolumny, a rozwinięcie kolumny Data bez filtra daje dokładne duplikaty.

Jak to rozwiązać krok po kroku

1. Kliknij krok Źródło i przejrzyj listę obiektów skoroszytu: które dane są tabelami, które arkuszami, czy są arkusze z wartością true w kolumnie Hidden i nazwane zakresy. Jeśli za krokiem Źródło masz już rozwinięcie i dalsze kroki czyszczenia, kliknij krok Źródło prawym przyciskiem i wybierz Wstaw krok po, żeby dodać filtr przed rozwinięciem.
2. Wariant z tabelami, zalecany, gdy każdy arkusz ma swoją tabelę: otwórz filtr kolumny Kind i zostaw zaznaczoną tylko wartość Table. Formuła kroku: Table.SelectRows(Źródło, each [Kind] = "Table"). Każda nowa tabela dodana do skoroszytu wejdzie do wyniku bez zmiany filtra.
3. Wariant z arkuszami, gdy danych nie sformatowano jako tabele: filtr musi odrzucić arkusze ukryte i pomocnicze, na przykład Table.SelectRows(Źródło, each [Kind] = "Sheet" and [Hidden] = false and [Name] <> "Słowniki"). W kroku Źródło zmień też drugi argument funkcji Excel.Workbook z null na true, żeby pierwszy wiersz każdego arkusza stał się nagłówkiem.
4. Rozwiń kolumnę Data: kliknij ikonę rozwijania w jej nagłówku, zaznacz kolumny Sklep, Kod produktu i Ilość, odznacz Użyj oryginalnej nazwy kolumny jako prefiksu i kliknij OK. Gdy pod listą kolumn widzisz napis Lista może być niekompletna, kliknij Załaduj więcej, żeby Power Query przejrzał wszystkie obiekty.
5. Zostaw kolumnę Name i nadaj jej czytelną nazwę, na przykład Arkusz, przyciskiem Zmień nazwę na karcie Przekształć. Po odświeżeniu wiesz dzięki niej, z którego arkusza pochodzi każdy wiersz. Pozostałe kolumny listy (Item, Kind, Hidden) usuń.
6. Typy danych ustaw dopiero na wyniku rozwinięcia: zaznacz kolumny i na karcie Przekształć kliknij Wykryj typ danych.

Jak sprawdzić, że zadziałało

Policz wiersze wyniku i porównaj z liczbą wierszy danych na arkuszach, bez nagłówków. W naszym skoroszycie po filtrze Kind równym Table zostało 5 wierszy, a suma kolumny Ilość wyniosła 9, tyle samo co na arkuszach. Wariant z arkuszami i nagłówkami z pierwszego wiersza dał te same 5 wierszy z arkuszy Styczeń i Luty. Sprawdź jeszcze, że kolumna z nazwą arkusza zawiera tylko arkusze albo tabele z danymi, a filtr kolumny Kod produktu nie pokazuje tekstu Kod produktu ani pustych pozycji.

Żeby problem nie wrócił, trzymaj dane każdego arkusza w tabeli Excela i filtruj po rodzaju obiektu, a nie po liście nazw. Jeśli w skoroszycie są też inne tabele, na przykład słowniki, nadaj tabelom z danymi wspólny początek nazwy i dodaj go do filtra. Pamiętaj, że Excel.Workbook czyta plik zapisany na dysku, więc nowy arkusz pojawi się w wyniku po zapisaniu skoroszytu źródłowego i odświeżeniu zapytania.

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