Pliki eksportowane z różnych systemów mogą tworzyć wiele wyzwań. Niepotrzebne początkowe wiersze i podsumowania omawiałem już na przykładzie pliku SAP. Czasem zdarza się, że nagłówki są w 2 wierszach, części nagłówków nie ma, a kolumn jest sporo. Jak poradzić sobie z takim zagadnieniem w Power Query?
Krok 1: Usuń niepotrzebne wiersze
Możesz to uczynić operacją Usuń pierwsze wiersze i Usuń końcowe wiersze lub zwykłym filtrowaniem np. wartości null. Zachowaj przy tym wszystkie wiersze, które składają się na nagłówki.
Krok 2: Zbuduj 2 odwołania
Jedno odwołanie będzie procesować nagłówki, drugie – dane właściwe.
Krok 3: Usuń symetrycznie wiersze
W naszym przypadku w zapytaniu Nagłówki zostawiam 2 pierwsze wiersze, a w zapytaniu Dane usuwam je.
Krok 4: Transponuj nagłówki
Czas na zabawę z transpozycją, dzięki której nagłówki będzie można potraktować jak kolumnę tekstową.
Krok 5: Złącz teksty
Czasem nagłówki nadają się wprost do złączenia, a czasem nie do końca i wtedy trzeba użyć jeszcze kolumny warunkowej czy zamiany wartości.
Krok 6: Wypełnij w dół
Wypełnianie jest jedną z najciekawszych opcji w Power Query i zastępuje stary trik: znajdź puste i = komórka wyżej z Ctrl + Enter. Trzeba tylko pamiętać, że potrzebna jest wartość null, a jeśli jej nie ma, zastępujemy tekst (1–>2 na karcie Przekształć) na null pisany małymi literami.
Krok 7: Usuń niepotrzebne kolumny i transponuj z powrotem
Krok 8: Do nagłówków dołącz dane jako nowe zapytanie
Nazwy nagłówków obu tabel nadal powinny zaczynać się od słowa Column.
Jeśli dobrze zrealizujemy cały proces, diagram zależności zapytań będzie wyglądał tak:
Krok 9: Nadaj nowe nagłówki
Teraz jest wreszcie moment na polecenie Użyj pierwszego wiersza jako nagłówków z karty Przekształć.
Sprawdź zapytanie Power Query w pliku Excel
Tu możesz pobrać plik Excel z zapytaniem Power Query naprawiającym nagłówki.
Masz inny problem z plikami tekstowymi lub Excelami?
Podziel się w komentarz.
Hello,
mam problem z nagłówkami, który pojawił się po ostatniej aktualizacji systemu (konkretnie JIRA). W każdym raporcie w pierwszej komórce generuje nazwę z inną datą. Zatem co miesiąc mam raport z innym pierwszym nagłówkiem. Pozostałe kolumny wyświetlają się prawidłowo już w wierszu trzecim (wszystkie identyczne co miesiąc). Power Query nie chce przekształcać danych z takiego raportu, chyba że ręcznie usunę pierwszy wiersz. Czy istnieje jakiś sposób, żeby można było to zrobić automatycznie w PQ? Z góry dziękuję za odpowiedź
Cześć Iza, na pierwszym kroku Użyj nagłówków jako pierwszego rzędu – wtedy opuścisz tę komórkę i będziesz mogła usunąć 4 wiersze razem z nią. Ewentualnie usuń automatyczny krok tworzenia nagłówków.
Witam,
w jaki sposób dodano „Nazwa” do nowo uzupełnionych wartości po Kroku 6.
Witam,
znalazłem takie rozwiązanie:
Krok 6a.
#”Dodano kolumnę warunkową” = Table.AddColumn(#”Wypełniono w dół”, „Nagłówek”, each if [Scalone] = [Column2] or [Scalone] = [Column1] then [Scalone] else [Scalone]&”Nazwa”)
Witam,
jak poradzić sobie takim zagadnieniem.
Dane jak w pliku wejściowym:
– komórka A1 = „Data raportu”;
– komórka A2 = 2019-12-03;
– komórka J5 = „Wartość”;
– wiersz 6 zawiera pozostałe nagłówki.
Chcemy żeby wszystkie nagłówki były z wiersza 5 i 6 (to zostało opisane w poradzie) oraz
dodać kolumnę o nagłówku z A1 i danymi z komórki A2.
Hej, odpowiedź na to pytanie znajdziesz w webinarze: https://skuteczneraporty.pl/12-wyzwan-power-query-wideo-z-rozwiazaniem-zadan/
Dziękuję za omówienie zagadniania. Fajny pomysł z tymi dwoma odwołaniami, transponowaniem itd. Ja jeszcze musiałam dodatko popracować przed wykonaniem kroku 8, aby nazwy nagłowków obu tabel zaczynały się od słowa Column. Ja bowiem potrzebuję w efekcie mieć 2 dodatkowe kolumny z nazwą pliku i arkusza, gdyż łączę co miesiąc (tydzień) pliki mające kilka lub kilkanaście arkuszy i potrzebuję mieć nazwy miesiąca (tygodnia) oraz nazwy tych arkuszy, z których pochodzą informacje. W zapytaniu „Dane” utworzyły mi się odpowiednio kolumny „Name” i „Name.1. Musiałam zmienić w rezultacie nagłówki wszystkich kolumn w zapytaniu „Dane”, aby od początku szło Column.1, Column.2 itd. Czy da radę ewentualnie jakoś to uprościć czy trzeba „ręcznie” to rzeźbić?
Cześć Jola, zawsze można opuścić nagłówki piętro niżej (Przekształć > Użyj nagłówków jako pierwszego rzędu) i usunąć pierwszy wiersz. Można też zmienić nazwy kolumną formułą języka M, niezależnie od ich pierwotnej nazwy:
= Table.RenameColumns(
#”Zmieniono typ”,
{
{Table.ColumnNames(#”Zmieniono typ”){0}, „Production Quantity”}
}
)
Bartku,
dziękuję za kod na okoliczność zmiany nazwy nagłowka kolumny, super się przyda! Muszę jeszcze wypróbować opuszczanie niżej nagłówków! Serdecznie dziękuję za to, iż dzielisz się wiedzą. Pozdrawiam.
Dzięki!