Anuluj przestawienie pomija nowe kolumny - zaznaczone kolumny czy inne kolumny

Power Query w Excelu, Power BI i Fabric

Anuluj przestawienie pomija nowe kolumny - zaznaczone kolumny czy inne kolumny

W skrócie

Na ten problem trafiasz przy danych w układzie szerokim, takich jak budżet z miesiącami w kolumnach: zapytanie działa, dopóki w pliku nie pojawi się nowa kolumna. W Power Query Anuluj przestawienie innych kolumn i Anuluj przestawienie tylko zaznaczonych kolumn dają dziś ten sam wynik, ale inaczej reagują na zmiany w źródle. Pokazujemy różnicę na budżecie fikcyjnej sieci Nordvella z naszego kursu, razem z drugą pułapką, czyli krokiem zmiany typu.

Jak to wygląda w praktyce

Budżet w kursie ma 15 wierszy i 13 kolumn: Sklep i dwanaście miesięcy. Po anulowaniu przestawienia powstaje 180 wierszy z kolumnami Sklep, Atrybut i Wartość. Kłopot pojawia się później: ktoś dopisuje w pliku nową kolumnę, a po odświeżeniu zapytanie nie zgłasza błędu, tylko nowa kolumna zostaje obok jako zwykła kolumna z liczbami i nie trafia do wierszy.

Sprawdziliśmy to w polskim Excelu na tabeli z kolumnami Sklep, Styczeń, Luty i Marzec, w której przestawienie anulowano tylko dla stycznia i lutego. Wynik miał kolumny Sklep, Marzec, Atrybut i Wartość. Marzec został z boku, więc raport oparty na kolumnie z nazwą miesiąca go nie uwzględni.

Druga odsłona tego samego problemu: po podmianie pliku na budżet z innymi nazwami kolumn zapytanie zatrzymuje się na kroku Zmieniono typ z komunikatem Expression.Error: Nie można znaleźć kolumny „Styczeń” w tabeli. (ang. The column 'Styczeń' of the table wasn't found.).

Dlaczego tak się dzieje

Menu Anuluj przestawienie kolumn ma trzy pozycje, które na tych samych danych dają ten sam wynik, ale zapisują w kroku co innego:

• Anuluj przestawienie innych kolumn zapisuje kolumny zaznaczone, czyli stałe, i przestawia wszystkie pozostałe. W kursie formuła ma postać Table.UnpivotOtherColumns(#"Zmieniono typ", {"Sklep"}, "Atrybut", "Wartość").
• Anuluj przestawienie tylko zaznaczonych kolumn zapisuje listę przestawianych kolumn, czyli nazwy miesięcy, w funkcji Table.Unpivot. Kolumny dodanej później nie ma na tej liście.
• Anuluj przestawienie kolumn według opisu polecenia w Excelu przestawia wszystkie kolumny oprócz niezaznaczonych. Dokumentacja Microsoft potwierdza, że przy odświeżeniu obejmuje też nowe kolumny.

W naszym teście dwa sklepy i trzy miesiące dały po Table.UnpivotOtherColumns 6 wierszy, a po Table.Unpivot z listą stycznia i lutego tylko 4 wiersze i osobną kolumnę Marzec.

Druga przyczyna siedzi wyżej. Krok Zmieniono typ dodany po imporcie tabeli wymienia każdą kolumnę z nazwy. W kursie jego formuła zaczyna się od Table.TransformColumnTypes(tBudzet_Table, {{"Sklep", type text}, {"Styczeń", Int64.Type}, {"Luty", Int64.Type} i wymienia kolejne miesiące. Nowa kolumna nie dostaje w nim typu, a gdy któraś z wymienionych kolumn zniknie albo zmieni nazwę, krok zgłasza błąd.

Jak to rozwiązać krok po kroku

1. Kliknij krok anulowania przestawienia na liście Zastosowane kroki i sprawdź pasek formuły. Jeśli widzisz Table.Unpivot z listą nazw miesięcy, czyli krok Anulowano przestawienie tylko zaznaczonych kolumn, usuń go krzyżykiem przy nazwie.
2. Zaznacz kolumny, które mają zostać bez zmian, w budżecie tylko Sklep. Gdy stałych kolumn jest więcej, dodawaj je do zaznaczenia z wciśniętym klawiszem Ctrl.
3. Na karcie Przekształć rozwiń Anuluj przestawienie kolumn i wybierz Anuluj przestawienie innych kolumn. Krok Anulowano przestawienie innych kolumn zapisze w formule tylko kolumny stałe, w kursie {"Sklep"}.
4. Zmień nazwy nowych kolumn dwuklikiem w nagłówku, w kursie Atrybut na Miesiąc i Wartość na Budżet.
5. Popraw krok Zmieniono typ przed anulowaniem przestawienia: usuń go albo zostaw w nim tylko kolumny stałe, na przykład {{"Sklep", type text}}. Typ liczbowy ustaw raz, po anulowaniu przestawienia, na kolumnie z wartościami. W naszym teście wartości zapisane jako tekst po takim kroku zsumowały się poprawnie do 220.
6. Pamiętaj, że wariant innych kolumn przestawi każdą nową kolumnę, także taką, która nie jest miesiącem, na przykład kolumnę z uwagami. Jeśli źródło może mieć takie kolumny, odfiltruj ich nazwy w kolumnie Miesiąc zaraz po anulowaniu przestawienia albo usuń je przed nim.

Jak sprawdzić, że zadziałało

Policz wiersze po anulowaniu przestawienia: powinno ich być tyle, ile wierszy źródła razy liczba przestawianych kolumn, w kursie 15 razy 12, czyli 180. Mniejsza liczba nie zawsze oznacza błąd. W naszym teście Table.UnpivotOtherColumns nie utworzyło wiersza dla komórki z null, więc sklep z wartością tylko w jednym z dwóch miesięcy dał jeden wiersz. Lista wartości w filtrze kolumny Miesiąc powinna zawierać wszystkie miesiące z pliku i nic więcej.

Najpewniejszy test to symulacja zmiany: dopisz w tabeli źródłowej kolumnę z kolejnym miesiącem, odśwież zapytanie i sprawdź, czy nowy miesiąc pojawił się w kolumnie Miesiąc, a nie jako osobna kolumna. Potem usuń testową kolumnę ze źródła.

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