Podziel kolumnę według ogranicznika gubi części przy nowych danych

Power Query w Excelu, Power BI i Fabric

W skrócie

W Power Query Podziel kolumnę według ogranicznika zapisuje w kroku stałą liczbę kolumn. Gdy nowy plik ma w komórce więcej części, nadmiarowe znikają bez błędu i bez komunikatu.
Krok pamięta listę nazw kolumn z chwili tworzenia, na przykład Kody.1 i Kody.2. Funkcja Table.SplitColumn wypełnia tylko te kolumny, a kolejne części odrzuca.
Gdy liczba części się zmienia, dziel do wierszy (Opcje zaawansowane, Podziel na: Wiersze) albo wylicz listę nazw kolumn w kodzie M z największej liczby części i kontroluj ją po każdym odświeżeniu.

Na ten problem trafiasz, gdy jedna komórka przechowuje listę wartości: kody produktów zamówienia, adresy e-mail albo tagi rozdzielone przecinkami. W Power Query Podziel kolumnę według ogranicznika działa przy pierwszym imporcie poprawnie, a po kolejnym odświeżeniu część danych znika. Pokazujemy, skąd się to bierze, jak dzielić do wierszy, jak wyliczyć liczbę kolumn z danych i jak kontrolować liczbę części.

Jak to wygląda w praktyce

Przykład: w styczniowym eksporcie zamówienia mają najwyżej dwa kody produktów w kolumnie Kody, rozdzielone przecinkiem. Na karcie Przekształć wybierasz Podziel kolumny, a potem Według ogranicznika, i powstają kolumny Kody.1 i Kody.2. W lutym pojawia się zamówienie z trzema kodami. Po odświeżeniu nie ma błędu ani nowej kolumny, a trzeci kod nie trafia nigdzie.

Sprawdziliśmy to w polskim Excelu. Krok podziału z dwiema nazwami kolumn zastosowany do tekstu „ZEN-1010,NOR-1030,KEL-1029” zwrócił kolumny Kody.1 i Kody.2 z wartościami ZEN-1010 i NOR-1030. Kod KEL-1029 zniknął, a zapytanie odświeżyło się bez ostrzeżenia. Pozycje z pominiętych części nie trafiają do dalszych kroków ani do raportu.

Dlaczego tak się dzieje

W chwili tworzenia kroku Power Query patrzy na dane, które ma w podglądzie. Według dokumentacji Microsoft dzieli kolumnę na tyle kolumn, ile wtedy potrzeba, i nadaje im nazwy z przyrostkiem złożonym z kropki i numeru części. Lista nazw zostaje jednak zapisana w kroku na stałe. Przy kolejnym odświeżeniu funkcja Table.SplitColumn wypełnia tylko te kolumny, a nadmiarowe części odrzuca.

Table.SplitColumn(Źródło, "Kody",
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
    {"Kody.1", "Kody.2"})
// dla tekstu ZEN-1010,NOR-1030,KEL-1029:
// Kody.1 = ZEN-1010, Kody.2 = NOR-1030, KEL-1029 znika

Liczbę kolumn widać też w oknie podziału: w Opcjach zaawansowanych jest pole Liczba kolumn, na którą zostanie podzielona kolumna. Gdy w formule zamiast listy nazw stoi liczba, efekt jest ten sam: z liczbą 2 w ostatnim argumencie trzecia część także zniknęła.

Jak to rozwiązać krok po kroku

Kliknij na liście Zastosowane kroki krok Podzielono kolumnę według ogranicznika i sprawdź w pasku formuły ostatni argument funkcji Table.SplitColumn. Lista w rodzaju {"Kody.1", "Kody.2"} oznacza, że krok zawsze zwróci dokładnie dwie kolumny.
Jeśli kolejne wartości z komórki mogą trafić do osobnych wierszy, usuń ten krok i podziel kolumnę jeszcze raz: Przekształć, Podziel kolumny, Według ogranicznika. W oknie Dzielenie kolumny według ogranicznika wybierz ogranicznik, zaznacz Każde wystąpienie ogranicznika, rozwiń Opcje zaawansowane i w sekcji Podziel na zaznacz Wiersze.
Podział do wierszy nie ma limitu liczby części. W kodzie M ten sam efekt daje Table.ExpandListColumn(Table.TransformColumns(Źródło, {{"Kody", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}}), "Kody"). W naszym teście dwa zamówienia z dwoma i trzema kodami dały 5 wierszy, po jednym na kod, z numerem zamówienia w każdym wierszu. To układ długi, który w kursie zalecamy przed scalaniem i grupowaniem.
Jeśli potrzebujesz kolumn, wylicz ich liczbę z danych. W Edytorze zaawansowanym (karta Strona główna) dodaj przed krokiem podziału kroki Liczba = List.Max(List.Transform(Źródło[Kody], each List.Count(Text.Split(_, ",")))), i Nazwy = List.Transform({1..Liczba}, each "Kod." & Text.From(_)),, a w kroku podziału zamień stałą listę na Nazwy. W naszym teście powstały kolumny Kod.1, Kod.2 i Kod.3, a zamówienie z dwoma kodami dostało null w trzeciej.
Dodaj kolumnę kontrolną: na karcie Dodaj kolumnę kliknij Kolumna niestandardowa i wpisz = List.Count(Text.Split([Kody], ",")). Kolumna pokaże liczbę części w każdym wierszu, a profil kolumny poda jej maksimum.
Jeśli potrzebujesz tylko pierwszej części, nie dziel kolumny. Użyj Dodaj kolumnę, Wyodrębnij, Tekst przed ogranicznikiem. Wynik nie zależy od liczby części, a kolumna źródłowa zostaje bez zmian.

Okno Dzielenie kolumny według ogranicznika w Power Query z myślnikiem jako ogranicznikiem, opcją Każde wystąpienie ogranicznika i zwiniętą sekcją Opcje zaawansowane — Dzielenie kolumny według ogranicznika z myślnikiem wpisanym jako ogranicznik niestandardowy i zaznaczoną opcją Każde wystąpienie ogranicznika. Opcje zaawansowane rozwijasz strzałką przy ich nazwie.

Jak sprawdzić, że zadziałało

Przy podziale do wierszy liczba wierszy po kroku powinna równać się sumie części ze wszystkich komórek. W naszym teście 2 + 3 dało 5 wierszy. Przy podziale do kolumn liczba kolumn wynikowych powinna być równa maksimum z kolumny kontrolnej, a ostatnia z nich musi mieć wartość przynajmniej w jednym wierszu. Krótsze komórki dostają null w brakujących kolumnach i to jest poprawny wynik.

Żeby problem nie wrócił, po odświeżeniu danych z nowego miesiąca zaglądaj do maksimum w kolumnie kontrolnej. W profilu kolumny przełącz profilowanie na cały zestaw danych, bo domyślnie obejmuje ono tylko pierwsze 1000 wierszy, a najdłuższa komórka może leżeć dalej.

Poczytaj więcej
Lekcja 4 bezpłatnego kursu Power Query: Kolumny niestandardowe, warunkowe i z przykładów
Cały bezpłatny kurs Power Query w Excelu, Power BI i Fabric z Copilotem

Wróć do listy: 88 najczęstszych pytań i problemów związanych z Power Query

Baner szkolenia Microsoft Excel - Power Query w JSystems z edytorem Power Query na ekranie laptopa

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

Autorzy

Tekst: Redakcja JSystems

Najczęściej zadawane pytania

Dlaczego Podziel kolumnę według ogranicznika gubi dane po odświeżeniu?

Krok zapisuje listę nazw kolumn wynikowych z chwili tworzenia. Gdy nowe dane mają więcej części, funkcja Table.SplitColumn wypełnia tylko zapisane kolumny, a nadmiarowe części odrzuca bez błędu. Dziel do wierszy albo wylicz liczbę kolumn z danych.

Jak w Power Query podzielić kolumnę na wiersze zamiast kolumn?

W oknie Dzielenie kolumny według ogranicznika rozwiń Opcje zaawansowane i w sekcji Podziel na zaznacz Wiersze. Każda część trafi do osobnego wiersza, a wartości pozostałych kolumn zostaną powtórzone. Taki podział nie ma limitu liczby części.

Co się dzieje, gdy komórka ma mniej części niż kolumn?

Brakujące kolumny dostają wartość null. W naszym teście zamówienie z dwoma kodami po podziale na trzy kolumny miało null w trzeciej kolumnie. To normalny wynik i nie oznacza błędu w danych.

Jak sprawdzić, ile części ma najdłuższa komórka?

Dodaj kolumnę niestandardową, która liczy części funkcjami List.Count i Text.Split, i sprawdź jej maksimum w profilu kolumny na całym zestawie danych. Ta sama liczba w kodzie M posłuży do zbudowania listy nazw kolumn. W naszym teście maksimum wynosiło 3.