Power Query: podział danych na kolumny według wzorca i długości tekstu

Kiedy ręczny podział w Excelu przestaje wystarczać

Gdzie pojawia się problem z podziałem tekstu

W wielu firmach dane z systemów trafiają do Excela w formie plików CSV, TXT lub raportów o stałej szerokości. Często kluczowe informacje są „zlepione” w jednej kolumnie: kody klientów, daty, typy transakcji, sufiksy kontrolne. Jednorazowe „Tekst jako kolumny” w Excelu działa przy pierwszym imporcie, ale przy kolejnym pliku trzeba powtarzać całą procedurę od zera.

Jeśli trzeba dzielić w ten sam sposób 10 plików dziennie lub zestaw 12 plików miesięcznych, ręczne operacje szybko stają się wąskim gardłem. Różnice w długości kodów, sporadyczne odstępstwa od formatu, dodatkowe spacje – to wszystko powoduje, że kopiowanie tej samej procedury przestaje być efektywne.

Ograniczenia jednorazowego „Tekst jako kolumny”

Funkcja „Tekst jako kolumny” w Excelu jest mocna, ale ma jedną krytyczną cechę: działa tylko w momencie wywołania. Nie zapisuje się jako reguła, która uruchamia się automatycznie podczas kolejnego odświeżenia danych. Jeśli zmieni się źródło lub dojdzie nowy plik, trzeba pamiętać o ponownym klikaniu kreatora podziału.

Dodatkowo Excel zakłada raczej proste przypadki: stały separator (np. średnik) lub równe szerokości kolumn. Gdy pojawia się kod o zmiennej długości, część logiczna musi być „dopisana w głowie” użytkownika, bo kreator nie rozumie niestandardowych wzorców ani warunków typu „jeśli zaczyna się na A, utnij po 5 znaku, w przeciwnym razie po 7”.

Delimiter kontra podział według długości i wzorca

W Power Query można dzielić kolumny po ograniczniku (delimiterze), według liczby znaków lub według pozycji. Dzielenie po ograniczniku działa idealnie przy plikach CSV czy zapisach „ID;Nazwa;Kwota”. Gdy jednak dane pochodzą z systemów mainframe, systemów księgowych lub starszych aplikacji, zamiast separatorów mamy ciągi o stałej (lub prawie stałej) długości.

Różnica jest kluczowa:

• dzielenie po delimiterze: szukasz konkretnego znaku lub ciągu znaków (np. „|” lub „;”) i tniesz tam, gdzie go znajdziesz;
• dzielenie według długości: tniesz po określonej liczbie znaków, niezależnie od tego, co w nich jest;
• dzielenie według wzorca: tniesz tam, gdzie spełnione jest pewne kryterium (np. zmiana typu znaku z litery na cyfrę, pojawienie się prefiksu „-PL-”, określony układ liter i cyfr).

Power Query pozwala zautomatyzować wszystkie trzy podejścia, a przy użyciu funkcji M można zbudować logikę dopasowaną dokładnie do rzeczywistego formatu danych, bez każdorazowego klikania kreatora.

Co daje przejście z Excela do Power Query

Największy zysk to powtarzalność. Reguły podziału zapisują się jako kroki w zapytaniu. Po podmienieniu pliku wejściowego cała sekwencja dzielenia kolumn, czyszczenia i filtrowania wykona się automatycznie. Nie ma potrzeby „rzeźbienia” za każdym razem.

Drugi zysk to możliwość obsługi wyjątków. Funkcje M pozwalają w prosty sposób zabezpieczyć się przed krótszymi ciągami, brakującymi kodami czy nietypowymi wierszami. To znacznie ogranicza ryzyko błędów przy odświeżaniu danych z kolejnych plików.

Podstawy dzielenia tekstu w Power Query – od interfejsu do M

Standardowe opcje „Podziel kolumnę”

W oknie edytora Power Query (Excel lub Power BI) przy każdej kolumnie tekstowej dostępna jest komenda Podziel kolumnę. Oferuje kilka trybów:

• według ogranicznika – odpowiednik „Tekst jako kolumny” po separatorze w Excelu;
• według liczby znaków – podział po określonej liczbie znaków (np. co 3 znaki);
• według pozycji – cięcie na zdefiniowanych pozycjach (np. po 4 i po 10 znaku);
• według małej/dużej litery – rozdzielenie na granicach zmiany wielkości liter.

W prostych przypadkach te opcje wystarczą. Problem pojawia się, gdy tekst ma części o zmiennej długości, a delimiter nie jest jednoznaczny lub w ogóle nie występuje. Wtedy trzeba zejść poziom niżej – do kodu M.

Jak podejrzeć i zrozumieć kod M

Każda operacja wykonana z interfejsu generuje krok w języku M. Kod można podejrzeć w Widok > Edytor zaawansowany. Przy podziale kolumn pojawi się zwykle funkcja Table.SplitColumn z odpowiednimi parametrami (delimiter, liczba znaków lub pozycje).

Zrozumienie struktury tego kroku pozwala:

• modyfikować parametry podziału bez klikania od nowa,
• podmienić prosty podział na bardziej zaawansowaną logikę (np. z użyciem funkcji tekstowych),
• łatwiej kopiować schemat podziału między różnymi zapytaniami.

W praktyce często wygodnie jest najpierw użyć interfejsu, a potem w Edytorze zaawansowanym skorygować szczegóły (np. zastąpić stałą wartość położenia wynikiem funkcji Text.PositionOf).

Podział wiersza na kolumny a nowe kolumny z częściami tekstu

Trzeba odróżnić dwa podejścia:

• Split Column – rozbija jedną kolumnę na kilka, usuwając oryginał lub go zastępując;
• Dodaj kolumnę niestandardową – tworzy nową kolumnę z funkcją, np. Text.Start, pozostawiając oryginalny tekst bez zmian.

Przy skomplikowanych wzorcach lepiej budować osobne kolumny niestandardowe dla kolejnych fragmentów tekstu. Daje to większą kontrolę, pozwala opatrywać kolumny czytelnymi nazwami i etapami oraz ułatwia debugowanie: jeśli coś nie działa, widać od razu, która konkretna formuła generuje błędne wyniki.

Dopiero na końcu, gdy logika działa stabilnie, można usunąć niepotrzebną oryginalną kolumnę, aby uprościć strukturę tabeli wyjściowej.

Ograniczenia standardowego „Split Column”

Wbudowane opcje „Podziel kolumnę” są szybkie, ale mają istotne ograniczenia:

• cięcie według liczby znaków zakłada stałą długość segmentów;
• cięcie według pozycji wymaga ręcznego wpisania konkretnych indeksów, które nie zmieniają się dynamicznie;
• brak natywnego wsparcia dla złożonych warunków (np. inne pozycje startowe dla różnych typów rekordów).

Dlatego w praktyce często opłaca się potraktować „Split Column” jako narzędzie pomocnicze, a właściwą logikę oprzeć na funkcjach tekstowych M w kolumnach niestandardowych lub warunkowych.

Kluczowe funkcje tekstowe M do pracy z długością i pozycją

Text.Length – baza do wszystkich obliczeń

Funkcja Text.Length zwraca długość ciągu znaków. Działa prosto:

Text.Length([Kod])

Bez tej funkcji trudno zbudować bezpieczną logikę, szczególnie przy danych o zmiennej długości. Typowe zastosowania:

• kontrola, czy ciąg jest wystarczająco długi, zanim użyjesz Text.Middle,
• ustalanie pozycji końcowej segmentu od końca tekstu (np. Text.Length – 3),
• tworzenie kolumn warunkowych, które reagują na różne długości kodów.

Dobrym nawykiem jest dodawanie prostych zabezpieczeń, np. jeśli długość jest mniejsza niż oczekiwany segment, zwróć pusty tekst lub null zamiast powodować błąd kroku.

Text.Start, Text.End, Text.Middle, Text.Range – prosty schemat użycia

Cztery najważniejsze funkcje do cięcia tekstu:

• Text.Start(tekst, n) – zwraca pierwsze n znaków;
• Text.End(tekst, n) – zwraca ostatnie n znaków;
• Text.Middle(tekst, pozycja, n) – zwraca n znaków od wskazanej pozycji (indeks od 0);
• Text.Range(tekst, pozycja, [n]) – podobna do Middle, ale trzeci parametr jest opcjonalny; jeśli go pominiesz, zwróci tekst od pozycji do końca.

Przykład prostego rozbicia kodu ABC1234567XYZ na trzy segmenty:

• pierwsze 3 znaki: Text.Start([Kod], 3) → ABC,
• kolejne 7 znaków (od pozycji 3): Text.Middle([Kod], 3, 7) → 1234567,
• ostatnie 3 znaki: Text.End([Kod], 3) → XYZ.

Różnica między Text.Middle a Text.Range jest subtelna, ale przydatna. Gdy segment sięga zawsze do końca ciągu (np. „reszta po prefiksie”), wygodniej użyć Text.Range tylko z dwoma parametrami.

Dynamiczne pozycje z Text.PositionOf i Text.PositionOfAny

Funkcje pozycjonujące są kluczem do podziału według wzorca:

• Text.PositionOf(tekst, szukany, [occurrence]) – zwraca indeks pierwszego wystąpienia szukanego ciągu;
• Text.PositionOfAny(tekst, listaSzukanych, [occurrence]) – podobnie, ale przyjmuje listę możliwych ciągów lub znaków.

Przykład: w kodzie PL-12345-AB chcesz odciąć wszystko przed pierwszym myślnikiem. Najpierw znajdź jego pozycję:

pos = Text.PositionOf([Kod], "-")

Potem pobierz wszystko przed nim:

Text.Start([Kod], pos)

Jeśli separator pojawia się wielokrotnie, można użyć parametru occurrence (np. Occurrence.Last) lub drugi raz Text.PositionOf na skróconej wersji tekstu. To pozwala budować elastyczne reguły nawet przy niejednoznacznych wzorcach.

Prosty schemat rozbijania tekstu o stałej strukturze

Dla tekstu typu ABC1234567XYZ o stałej strukturze można przyjąć prosty schemat:

1. oznaczyć długości segmentów (np. 3, 7, 3),
2. zdefiniować kolejne kolumny z Text.Start / Text.Middle / Text.End,
3. dodać ewentualne zabezpieczenia na długość całego ciągu.

Takie podejście dobrze sprawdza się dla raportów z systemów bankowych, billingów, plików z maszyn, w których każdy rekord ma identyczny układ pól. Gdy logika jest stabilna, można ją łatwo skopiować do innych zapytań lub zastosować przy imporcie folderu z wieloma plikami.

Dzielenie na kolumny według stałej długości segmentów

Stała szerokość – klasyczny przypadek z systemów legacy

Pliki o stałej szerokości to częsty format w starszych systemach lub integracjach, gdzie każdy rekord ma ściśle zdefiniowany układ znaków. Przykład jednego ciągu:

12345620230101AB000100

Załóżmy następujący układ:

• 6 znaków – numer klienta,
• 8 znaków – data w formacie RRRRMMDD,
• 2 znaki – kod typu,
• 4 znaki – kwota w skróconym zapisie.

Budowanie kolumn z Text.Start, Text.Middle i Text.End

Po załadowaniu danych do Power Query tworzysz kolejne kolumny niestandardowe:

• Numer klienta: Text.Start([Rekord], 6)
• Data surowa: Text.Middle([Rekord], 6, 8)
• Kod typu: Text.Middle([Rekord], 14, 2)
• Kwota surowa: Text.End([Rekord], 4)

Po sprawdzeniu rezultatów warto skonwertować:

• „Data surowa” na typ Data,
• „Kwota surowa” na liczbowy (być może dzieląc przez 100 lub inny współczynnik, jeśli taki obowiązuje w systemie).

Taki zestaw kroków działa identycznie dla każdego nowego pliku o tej samej strukturze, niezależnie od długości całego raportu.

Przykład: numer klienta + data + kod typu

Załóżmy prostszy ciąg:

87654320221231FV

Struktura:

• 6 znaków – numer klienta,
• 8 znaków – data transakcji,
• 2 znaki – kod typu dokumentu.

Formuły w kolumnach niestandardowych:

• Klient: Text.Start([Kod], 6)
• DataTxt: Text.Middle([Kod], 6, 8)

Kontrola błędów przy stałej długości

Stała długość nie oznacza, że dane zawsze są poprawne. Zdarzają się krótsze rekordy, brakujące znaki lub dodatkowe spacje. Bez zabezpieczeń funkcje tekstowe potrafią zwrócić błąd i zablokować odświeżanie całego zapytania.

Prosty schemat ochronny opiera się na porównaniu długości ciągu z oczekiwaną wartością:

• wyliczenie długości: len = Text.Length([Rekord]),
• warunek: jeśli len < 20, zwróć null lub pusty tekst,
• w przeciwnym razie wykonaj Text.Start / Text.Middle / Text.End.

Przykładowa kolumna niestandardowa z warunkiem:

if Text.Length([Rekord]) >= 6 then Text.Start([Rekord], 6) else null

Takie warunki lepiej dodać od razu dla kluczowych segmentów, szczególnie jeśli pliki pochodzą z różnych źródeł lub z ręcznych eksportów.

Stała długość + zmieniająca się długość końcówki

Częsty układ: stały prefiks i zmienny sufiks, np. 10 znaków identyfikatora, potem dowolna liczba znaków komentarza. W takiej sytuacji nie ma sensu sztywno ciąć końcówki.

Przykład ciągu:

ABC1234567Brak płatności

Pierwsze 10 znaków to identyfikator, pozostała część to opis:

• Id: Text.Start([Kod], 10)
• Opis: Text.Range([Kod], 10)

Warunek bezpieczeństwa można oprzeć o długość:

if Text.Length([Kod]) >= 10 then Text.Start([Kod], 10) else null

Dla opisu:

if Text.Length([Kod]) > 10 then Text.Range([Kod], 10) else ""

Podział według wzorca: rozpoznawanie fragmentów po znakach i typach

Łączenie stałych pozycji z separatorami

W wielu kodach część ma stałą długość, a część jest oddzielona znakiem, np.:

PL876543-2022-12-31

Kod kraju i numer klienta są zawsze tej samej długości, natomiast data bywa zapisana z myślnikami lub bez. Sprawdzony sposób to:

1. wycięcie stałej części prefiksu,
2. wyszukanie separatorów w reszcie ciągu,
3. podział według znalezionych pozycji.

Przykład:

• Kraj: Text.Start([Kod], 2)
• Reszta: Text.Range([Kod], 2)
• PozycjaMyślnika: Text.PositionOf([Reszta], "-")
• Klient: Text.Start([Reszta], [PozycjaMyślnika])

Dalej można już pracować na fragmencie po pierwszym myślniku:

Text.Range([Reszta], [PozycjaMyślnika] + 1)

Rozpoznawanie fragmentów po typie znaku

Gdy brak separatorów, przydaje się logika oparta o typy znaków: cyfry, litery, inne symbole. Załóżmy kod:

INV202312A5

Identyfikator składa się z:

• prefiksu literowego (np. „INV”),
• ciągu cyfr (rok + miesiąc),
• litery serii,
• końcowej cyfry wariantu.

Można zbudować funkcję, która skanuje tekst znak po znaku i sprawdza, kiedy kończą się litery, a zaczynają cyfry. W M nie ma wbudowanego regexa, ale da się to osiągnąć prostym algorytmem:

1. zamiana tekstu na listę znaków: Text.ToList([Kod]),
2. iteracja po liście z indeksem i filtrowanie według kryterium czy to liczba / czy to litera.

Przykładowe sprawdzanie, czy znak jest cyfrą:

List.Contains({"0".."9"}, znak)

Na tej podstawie można ustalić pozycję pierwszej cyfry:

posDigit = List.PositionOfAny(Text.ToList([Kod]), {"0".."9"})

Następnie:

• Prefiks literowy: Text.Start([Kod], posDigit)
• ResztaNumeryczna: Text.Range([Kod], posDigit)

Text.Select i Text.Remove – szybkie wyciąganie tylko cyfr lub tylko liter

Gdy nie trzeba odtwarzać pierwotnej kolejności segmentów, często wystarczy proste wyciągnięcie cyfr i liter. Służą do tego:

• Text.Select(tekst, listaZnaków) – zachowuje tylko podane znaki,
• Text.Remove(tekst, listaZnaków) – usuwa podane znaki.

Przykład:

Text.Select([Kod], {"0".."9"}) – zwróci wszystkie cyfry z ciągu.

Text.Remove([Kod], {"0".."9"}) – usunie wszystkie cyfry, zostawi litery i resztę.

Dla kodu INV202312A5:

• Text.Select([Kod], {"0".."9"}) → 2023125
• Text.Remove([Kod], {"0".."9"}) → INVA

Takie wyrażenia dobrze sprawdzają się przy szybkich ekstraktach: numerów dokumentów z opisów, identyfikatorów z nazw plików itp.

Podział według wielu potencjalnych separatorów

W praktyce separator bywa różny w zależności od źródła danych: raz myślnik, raz ukośnik, innym razem spacja. Zamiast obsługiwać każdy przypadek osobno, lepiej zebrać wszystkie możliwe znaki w jedną listę.

Przykład kodu:

2023/01/15, 2023-01-15, 2023 01 15

Wszystkie wersje można przeciąć tak samo:

sepList = {"/","-"," "}

pos1 = Text.PositionOfAny([DataTxt], sepList)

Następnie:

• Rok: Text.Start([DataTxt], pos1)
• Reszta: Text.Range([DataTxt], pos1 + 1)

Dalej ten sam pomysł stosuje się do miesiąca i dnia, znów wołając Text.PositionOfAny na fragmencie „Reszta”.

Kolumny niestandardowe i warunkowe – elastyczne dzielenie bez „Split Column”

Kolumna niestandardowa z logiką „jeśli kod zaczyna się od…”

Część systemów generuje różne typy rekordów w jednej kolumnie. Prefiks określa typ, a co za tym idzie – długości fragmentów. Da się to obsłużyć jedną kolumną niestandardową z warunkami.

Prosty przykład:

if Text.Start([Kod], 2) = "FV" then Text.Range([Kod], 2, 8) else Text.Range([Kod], 3, 6)

Można to zorganizować czytelniej, stosując zagnieżdżone if … then … else:

if Text.Start([Kod], 2) = "FV" then
    Text.Range([Kod], 2, 8)
else if Text.Start([Kod], 2) = "PA" then
    Text.Range([Kod], 2, 10)
else
    null

W ten sposób jedna kolumna „Numer dokumentu” wyciąga różne zakresy dla różnych typów kodów.

Kolumna warunkowa zamiast wielu kroków

Interfejs „Kolumna warunkowa” pozwala zbudować podobną logikę bez pisania M, choć zwykle kod i tak warto potem przejrzeć. Dobrze się sprawdza, gdy:

• typów rekordów jest kilka,
• dla każdego typu powtarza się podobny schemat, z innymi długościami.

Przykładowe reguły:

• Jeśli [Typ] = „A” → Text.Start([Kod], 5)
• Jeśli [Typ] = „B” → Text.Start([Kod], 7)
• W przeciwnym razie → null.

Po wygenerowaniu kolumny można zajrzeć do Edytora zaawansowanego i wprowadzić drobne korekty, np. dodać dodatkowe zabezpieczenia długości czy wykorzystywać już policzone wartości pośrednie.

Użycie let … in w jednej kolumnie

Gdy formuła zaczyna się rozrastać, czytelność ratuje konstrukcja let … in. Pozwala nazwać kroki pośrednie w obrębie jednej kolumny niestandardowej.

Przykład:

let
    txt = [Kod],
    len = Text.Length(txt),
    posDash = Text.PositionOf(txt, "-"),
    pref = if posDash > 0 then Text.Start(txt, posDash) else txt,
    suf = if posDash > 0 and len > posDash + 1 then Text.Range(txt, posDash + 1) else ""
in
    pref & " | " & suf

Dzięki temu łatwo sprawdzić poszczególne kroki, a w razie potrzeby skopiować część logiki do innej kolumny lub tabeli.

Budowanie „słownika pozycji” jako rekord lub tabela pomocnicza

Jeśli ten sam układ długości ma wiele kolumn, opłaca się wynieść parametry do osobnej struktury. Przykładowo:

Pozycje = [
    KlientStart = 0,
    KlientLen   = 6,
    DataStart   = 6,
    DataLen     = 8
]

Kolumna niestandardowa może potem wyglądać tak:

Text.Middle([Rekord], Pozycje[KlientStart], Pozycje[KlientLen])

Zmiana długości segmentu wymaga wtedy edycji tylko w jednym miejscu, co redukuje ryzyko pomyłek przy większych zapytaniach.

Dzielenie tekstu na kilku poziomach: łączenie różnych technik

Najpierw wiersze, potem kolumny

Bywa, że dane są spakowane w jednym rekordzie, rozdzielone znakiem końca linii lub innym separatorem. Wtedy pierwszy krok to rozbicie na wiersze:

• Split Column by Delimiter z opcją „Na wiersze”,
• lub funkcja Text.Split + Table.ExpandListColumn.

Po takim rozpakowaniu każdy wiersz ma już „czysty” ciąg, który można dalej rozcinać według długości lub wzorca. To typowa sytuacja przy imporcie plików logów lub pól notatek z systemów CRM.

Podział oparty o różne poziomy szczegółowości

Czasem trzeba najpierw wyodrębnić ogólny identyfikator, a dopiero z niego kolejne pola. Przykład:

PL-123456-20230115-AB

Dobry, prosty schemat:

1. podział po myślnikach na cztery części (kraj, numer, data, typ),
2. zamiana daty na typ Data (kolejne cięcia lub bezpośrednia konwersja),
3. ewentualne rozbicie numeru klienta wg stałej długości.

Można to zrealizować bez „Split Column”, budując kolejne kolumny niestandardowe:

• pos1 = Text.PositionOf([Kod], "-")
• pos2 = Text.PositionOf([Kod], "-", pos1 + 1)
• i tak dalej.

Tymczasowe kolumny pomocnicze

Przy wieloetapowym dzieleniu lepiej nie próbować zmieścić całego algorytmu w jednej formule. Łatwiej dodać kilka prostych kolumn pomocniczych, np.:

• „Prefiks” – pierwsze 3 znaki,
• „Reszta” – Text.Range od pozycji 3,
• „PozycjaMyślnika” – indeks separatora w „Reszcie”,
• „Id klienta” – Text.Start na „Reszcie”.

Na końcu ścieżki można te kolumny usunąć, żeby tabela wynikowa była czysta. Edytor kroków i tak przechowuje całą historię.

Łączenie cięcia według długości z walidacją wzorca

Stała długość segmentów nie gwarantuje, że w środku są właściwe znaki. Dla kodów, które muszą składać się wyłącznie z cyfr lub liter, dobrze jest od razu dodać prostą walidację.

Przykład dla 6-cyfrowego numeru:

let
    segment = Text.Start([Rekord], 6),
    onlyDigits = Text.Select(segment, {"0".."9"})
in
    if Text.Length(onlyDigits) = 6 then segment else null

Dzięki temu błędne rekordy nie przechodzą dalej jako „legalne” numery klienta, lecz można je wyłapać w osobnym kroku filtrując null.

Praca na wielu plikach – gdy wzorzec jest ten sam, a dane rosną

Folder jako źródło i jedna logika podziału

Łączenie plików z folderu i zastosowanie jednego kroku dzielenia

Przy źródle typu Folder najpierw buduje się standardowy schemat:

1. Źródło → Folder → wskazanie lokalizacji,
2. „Połącz pliki” → przykład pliku → Edytor Power Query,
3. konfiguracja dzielenia kolumn na przykładowym pliku,
4. powrót do zapytania głównego, które powiela tę samą logikę dla wszystkich plików.

Kluczowy jest krok przykładowy (zwykle Transform Sample File), bo tam umieszcza się funkcje Text.Range, Text.Middle czy dodatkowe walidacje wzorca.

Jeżeli później do folderu trafi nowy plik z takim samym układem pól, żadna zmiana w Power Query nie jest potrzebna – wystarczy odświeżenie.

Parametryzacja nazwy i wzorca pliku

Często nie wszystkie pliki w folderze powinny być ładowane do jednego modelu. Dobrym filtrem jest wzorzec nazwy, np. tylko pliki zaczynające się od konkretnego prefiksu i roku.

Przykładowy krok filtrowania:

= Table.SelectRows(
    #"Źródło",
    each Text.StartsWith([Name], "LOG_")
        and Text.EndsWith([Name], ".txt")
)

Jeżeli nazwa zawiera datę lub typ, można ją odciąć podobnymi technikami jak resztę tekstu, np.:

let
    name = [Name],
    base = Text.BeforeDelimiter(name, "."),
    rok  = Text.End(base, 4)
in
    rok

Taka dodatkowa kolumna z datą ułatwia późniejsze filtrowanie miesięcy, kwartałów lub zakresów lat bez analizowania zawartości plików.

Stały układ rekordów w wielu plikach tekstowych

Przy plikach .txt z rekordami stałej szerokości sensownie jest najpierw wczytać całe wiersze bez dzielenia, a dopiero w kroku później rozcinać kolumny według pozycji.

Schemat:

1. Źródło → Folder → Połącz pliki,
2. w oknie łączenia ustawić separator na „brak” (cały wiersz do jednej kolumny, np. Content lub Column1),
3. w przykładzie pliku dodać kolumny niestandardowe z Text.Range, Text.Middle itd.,
4. na końcu usunąć kolumnę oryginalnego wiersza.

Przy zmianie szerokości jakiegoś pola wystarczy poprawić pojedynczą formułę; wszystkie pliki z folderu od razu korzystają z nowego „szablonu cięcia”.

Dzielenie rekordów z różnym układem w jednym folderze

Zdarza się, że w folderze mieszają się pliki z różnymi strukturami – np. różne lata mają inny layout. Wtedy proste kopiowanie logiki cięcia nie wystarczy.

Jednym z podejść jest wprowadzenie kolumny „Wersja” na podstawie nazwy pliku lub stałego fragmentu nagłówka:

if Text.Contains([Name], "_2019_") then "V1"
else if Text.Contains([Name], "_2020_") then "V2"
else "Unknown"

Dalej można:

• albo rozdzielić zapytanie na dwie tabele (V1 i V2) i każdą ciąć osobno,
• albo w jednym zapytaniu zastosować warunkowe formuły, np. różne Text.Range w zależności od wersji.

W drugim wariancie przydatna jest kolumna niestandardowa typu:

if [Wersja] = "V1" then
    Text.Range([Rekord], 0, 8)
else if [Wersja] = "V2" then
    Text.Range([Rekord], 2, 8)
else
    null

Takie rozwiązanie bywa prostsze niż zarządzanie kilkoma prawie identycznymi zapytaniami.

Testowanie logiki podziału na małej próbce

Przy folderach z dużą liczbą plików lepiej rozwijać logikę cięcia na małej próbce – np. 1–2 pliki. Oszczędza to czas odświeżania.

Podstawowa technika:

• po wczytaniu folderu zastosować filtr na kolumnie Name lub Extension,
• zbudować całą logikę,
• gdy działa – usunąć filtr i ewentualnie zastąpić go bardziej ogólnym (np. tylko konkretny rok).

Takie podejście upraszcza też szukanie błędów dzielenia w konkretnym pliku, bez konieczności przeglądania tysięcy rekordów jednocześnie.

Typowe błędy przy dzieleniu według długości i wzorca

Założenie, że wszystkie rekordy mają tę samą długość

Najczęstszy problem to ślepe przyjęcie, że każda linia ma identyczną liczbę znaków. W praktyce dochodzą puste spacje, brakujące pola, zbyt krótkie lub uszkodzone rekordy.

Zanim powstanie docelowe cięcie, dobrze jest dodać pomocniczą kolumnę:

Text.Length([Rekord])

Potem wystarczy szybki podgląd: sortowanie po długości, filtr na wartości skrajne, kilka reprezentatywnych przykładów. Często to wystarcza, by wychwycić rzadkie odstępstwa, których nie widać na pierwszych wierszach.

Sztywne indeksy bez zabezpieczeń granicznych

Funkcje Text.Start, Text.Range i Text.Middle nie zgłaszają błędu, gdy proszą o zbyt długi zakres – po prostu zwracają tyle znaków, ile się da. To z jednej strony wygodne, ale bywa zdradliwe.

Przykład:

Text.Range([Rekord], 10, 8)

Jeżeli Text.Length([Rekord]) < 18, wynik nadal będzie tekstem, lecz uciętym. Warto do krytycznych pól dodać prostą kontrolę:

let
    txt = [Rekord],
    len = Text.Length(txt)
in
    if len >= 18 then
        Text.Range(txt, 10, 8)
    else
        null

Taki null można potem odfiltrować lub przeanalizować w raporcie jakości danych.

Ignorowanie białych znaków i ukrytych separatorów

Przy imporcie z systemów legacy częstym problemem są spacje na końcu, tabulatory czy znaki nowej linii doklejone do rekordu. Proste porównania i cięcia zaczynają się rozjeżdżać.

Bezpieczniej jest wprowadzić krok standaryzujący:

• Text.Trim – obcina spacje z początku i końca,
• Text.Clean – usuwa niewidoczne znaki sterujące (jeśli używasz, trzeba dodać tę funkcję ręcznie w M),
• zamiana podwójnych spacji na pojedyncze, jeśli w danych się powtarzają.

Przykład:

let
    raw = [Rekord],
    trimmed = Text.Trim(raw),
    normalized = Text.Replace(trimmed, "  ", " ")
in
    normalized

Dopiero na takim, uproszczonym ciągu opłaca się budować logikę podziału.

Łączenie tekstu po cięciu bez kontroli spójności

Drugim, mniej oczywistym błędem jest budowanie końcowych kolumn przez sklejenie kilku wcześniej wyciętych segmentów bez sprawdzenia, czy wszystko zadziałało poprawnie.

Przykład:

FullCode = [Prefix] & [Id] & [Suffix]

Jeżeli [Id] jest null albo ma nieoczekiwaną długość, pełny kod wygląda poprawnie, ale w środku zawiera błąd. Prostsza kontrola:

if [Prefix] <> null
   and [Id] <> null
   and Text.Length([Id]) = 6
then
   [Prefix] & [Id] & [Suffix]
else
   null

Dzięki temu zapisane zostaną tylko kompletne kody, a resztę można zweryfikować osobno.

Mieszanie kodowania znaków przy imporcie z plików

Przy plikach tekstowych inny problem to błędne kodowanie (UTF-8 vs ANSI). Objawia się dziwnymi literami w nazwach, w tym w separatorach i prefiksach.

Jeśli separator ma być średnikiem, a w danych pojawia się znak podobny, ale z innym kodem, funkcje typu Text.Split nie znajdują go. Warto przy imporcie:

• sprawdzić ustawione kodowanie w kroku źródłowym,
• przetestować alternatywne ustawienia na jednym pliku,
• ewentualnie wprowadzić zamianę „podejrzanych” znaków na pewne, np.:

  Text.Replace([Rekord], "¦", ";")

Mylenie pozycji 0/1 przy wyznaczaniu indeksów

Funkcje pozycyjne M (np. Text.PositionOf, Text.Range) stosują indeksowanie od zera. W Excelu użytkownicy instynktownie liczą „od 1”, co łatwo prowadzi do przesunięć o jeden znak.

Przykład:

• „pierwszy znak po prefiksie 3-znakowym” → w M to pozycja 3,
• Text.Range([Kod], 3) – czwarty znak dla człowieka, ale faktycznie „od pozycji 3 (0,1,2,3)”.

Dobrą praktyką jest nazwanie pomocniczych zmiennych tak, by od razu sygnalizowały, jak są liczone:

posDash0 = Text.PositionOf([Kod], "-"),   // indeks 0-based
startAfterDash = posDash0 + 1

Dzięki temu po kilku miesiącach łatwiej odczytać własny kod, a poprawki są mniej ryzykowne.

Brak izolacji logiki wzorców w osobnych krokach

Rozbudowane formuły typu:

Text.Range([Kod], Text.PositionOfAny([Kod], {"-","/"}) + 1, 8)

na pierwszy rzut oka są efektowne, ale trudne w utrzymaniu. Ciężko tu od razu zobaczyć, jaka dokładnie część tekstu jest wycinana i dlaczego.

Czytelniejsza sekcja w kolumnie niestandardowej:

let
    txt = [Kod],
    posSep = Text.PositionOfAny(txt, {"-","/"}),
    part = Text.Range(txt, posSep + 1, 8)
in
    part

Jeśli w późniejszym kroku okaże się, że trzeba dodać kolejny separator lub zmienić długość fragmentu, wystarczy korekta jednej z nazwanych zmiennych, bez rozplątywania długiego pojedynczego wyrażenia.

Najczęściej zadawane pytania (FAQ)

Jak podzielić tekst na kolumny w Power Query według stałej liczby znaków?

Najprościej skorzystać z opcji interfejsu. Zaznacz kolumnę tekstową, wybierz: Transformuj > Podziel kolumnę > Według liczby znaków. Podaj liczbę znaków i sposób podziału (np. co 3 znaki, na tyle kolumn ile potrzeba).

Jeśli potrzebujesz większej kontroli, dodaj kolumny niestandardowe z funkcjami M, np. Text.Start([Kod],3), Text.Middle([Kod],3,4), Text.End([Kod],2). Dzięki temu łatwo zmienisz logikę bez ponownego klikania kreatora.

Jak automatycznie dzielić jedną kolumnę tekstu na kilka przy każdym odświeżeniu danych?

W Power Query każdy podział kolumny zapisuje się jako krok zapytania. Po pierwszej konfiguracji (Split Column lub kolumny niestandardowe) wystarczy podmienić plik źródłowy i kliknąć Odśwież – podział wykona się automatycznie.

Dla powtarzalnych plików (np. raport miesięczny) przygotuj jedno zapytanie z pełną logiką dzielenia, a potem tylko wskazuj kolejny plik lub folder. Unikniesz ręcznego uruchamiania „Tekst jako kolumny” w Excelu przy każdej aktualizacji.

Kiedy lepiej użyć „Podziel kolumnę”, a kiedy kolumny niestandardowej z funkcją Text.*?

„Podziel kolumnę” sprawdza się przy prostych przypadkach: stały delimiter, stała liczba znaków, znane pozycje cięcia. Daje szybki efekt bez pisania formuł, ale jest mało elastyczne, gdy format się zmienia.

Kolumny niestandardowe z funkcjami Text.Start, Text.Middle, Text.End, Text.Range dają pełną kontrolę nad logiką, pozwalają obsłużyć wyjątki i różne długości kodów. Przy raportach z systemów księgowych czy mainframe zwykle lepiej od razu budować osobne kolumny niestandardowe dla kluczowych segmentów tekstu.

Jak dzielić tekst w Power Query, gdy długość kodu jest zmienna?

Przy zmiennej długości kluczowa jest funkcja Text.Length. Najpierw sprawdzasz długość ciągu, a potem decydujesz, jak go uciąć. Przykład: jeśli [Kod] jest krótszy niż 10 znaków, zwróć null, inaczej pobierz konkretne segmenty.

Logikę możesz zapisać w kolumnie niestandardowej z warunkiem, np.: if Text.Length([Kod]) >= 5 then Text.Start([Kod],5) else null. Dzięki temu zapytanie nie „wywali się” na krótszych rekordach, tylko wstawi puste wartości.

Czym różni się dzielenie po delimiterze od dzielenia według pozycji lub wzorca?

Dzielenie po delimiterze (separatorze) szuka konkretnego znaku lub ciągu, np. „;” w plikach CSV, i tnie w miejscach jego wystąpienia. Sprawdza się, gdy plik ma klasyczną strukturę pól rozdzielonych separatorem.

Dzielenie według pozycji lub długości operuje na liczbie znaków, niezależnie od ich treści. W przypadku wzorca dochodzi dodatkowa logika, np. cięcie przy zmianie liter na cyfry albo po znalezieniu prefiksu „-PL-”. Takie podejście jest typowe dla raportów o stałej szerokości lub prawie stałej, gdzie separatorów po prostu nie ma.

Jak w Power Query rozdzielić kod na część tekstową i numeryczną, gdy nie ma separatora?

Najprostszy sposób to użycie funkcji tekstowych w kolumnach niestandardowych połączonych z warunkiem. Przykładowo możesz szukać pierwszej cyfry w ciągu (Text.PositionOfAny z listą cyfr), a potem użyć Text.Start i Text.Range do wydzielenia części literowej i liczbowej.

W wielu przypadkach wystarczy też opcja „Podziel kolumnę według małej/dużej litery”, jeśli format jest przewidywalny (np. litery, potem cyfry). Gdy reguły są bardziej złożone, potrzebna będzie własna funkcja M z kilkoma krokami logiki.

Jak podejrzeć i zmodyfikować kod M odpowiedzialny za podział kolumny?

W edytorze Power Query przejdź do Widok > Edytor zaawansowany. Znajdziesz tam wszystkie kroki, w tym te z Table.SplitColumn wygenerowane przez interfejs. Możesz ręcznie zmienić parametry podziału, np. delimiter, pozycje czy liczbę znaków.

Praktyczny schemat pracy: najpierw wykonaj prosty podział z menu Podziel kolumnę, a dopiero potem w Edytorze zaawansowanym dopasuj szczegóły, np. zastąp stałą pozycję wynikiem Text.PositionOf lub odejmij kilka znaków od Text.Length, żeby segment sięgał dynamicznie do końca kodu.