Samouczek Microsoft Power Query dotyczący rozwiązywania problemów z mieszanymi stałymi szerokościami kolumn

Mudassir: Na dzisiaj mamy bardzo interesujący problem do przepracowania. Problem z tym plikiem polega na tym, że jest on rozgraniczony kolumnami i nie wiem, jak rozwiązać ten problem za pomocą Microsoft Power Query. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Po pierwsze, dynamiczne usuwanie kolumn nie było dla mnie łatwe. Po drugie, w tym raporcie mamy jedną tabelę z inną szerokością kolumn, a następnie drugą tabelę z inną szerokością kolumn.

Jeśli więc dynamicznie zastosuję jeden ogranicznik na górze, nie będę w stanie uzyskać danych. Chcę uzyskać wszystkie te dane z drugiej tabeli, a moje numery produktów z pierwszej tabeli. Chcę również numer zadania w każdym wierszu wszystkich tabel.

Próbowałem rozwiązać to sam, ale ponieważ ma to coś wspólnego z zapytaniem o moc, potrzebowałem pomocy Melissy. Myślałem, że zajmie jej to co najmniej dwa dni, ale od razu znalazła rozwiązanie.

Melissa pokaże nam, jak rozwiązała ten skomplikowany problem. Myślę, że większość ludzi będzie miała do czynienia z tego rodzaju problemami i szuka sposobów ich rozwiązania.

Melissa: Pierwsza wskazówka jest taka, że ​​jeśli patrzysz na plik o stałej długości, możesz przejść do karty Widok i włączyć opcję Monospaced .

Widzimy, że jest to czcionka o stałej długości. Możemy również zobaczyć nagłówki, początkowe tabele i podtabele. To są części, które nas interesują i z których chcemy wyodrębnić.

Upewnij się również, że pasek formuły jest włączony. Zawsze dobrze jest mieć go widocznego na ekranie, ponieważ będziemy go często używać do wprowadzania niewielkich modyfikacji danych wejściowych.

Stworzyłem parametr dla lokalizacji pliku, w którym zapisałem plik CSV. Przyniosłem to jako plik pomostowy i umieściłem go w moim parametrze lokalizacji pliku. Następnie stworzyłem referencję i będę pracował z tym referencją. Właśnie temu przyglądamy się teraz w zapytaniu Microsoft power.

Spis treści

• Dodawanie kolumny indeksu
• Tworzenie listy buforów w Microsoft Power Query
• Dzielenie nagłówków z wierszy
• Usuwanie wartości zerowych i spacji w Microsoft Power Query
• Dzielenie sekcji
• Przycinanie ciągów tekstowych w Microsoft Power Query
• Wniosek

Dodawanie kolumny indeksu

Zwykle, kiedy zaczynam pracę nad takim plikiem, będę musiał poznać wymagania klienta. Pytam, czego klient potrzebuje, na co zwrócić uwagę.

W tym przypadku chcemy numer pozycji i numer zadania z nagłówków, a następnie chcemy wszystkie szczegóły, które należą do tego konkretnego nagłówka.

Będziemy potrzebować klucza, żeby połączyć te rzeczy z powrotem. Ale jeśli nie ma klucza, moim celem jest dodanie kolumny indeksu . Kliknę ikonę mini tabeli, wybierz Dodaj kolumnę indeksu , a następnie dodaj Od 0 .

Później użyję logiki z listami, które mają indeks oparty na 0. Początek indeksu od zera oznacza w rzeczywistości, że możesz odwoływać się do tego samego wiersza. W przeciwnym razie będziesz musiał odjąć 1, aby dostać się do tej pozycji opartej na 0.

Następnie musimy znaleźć pozycję, w której znajdują się nasze nagłówki, co możemy zrobić dość łatwo, ponieważ nagłówki te są stale powtarzane w całym pliku.

Na początek skopiujmy tę wartość:

Dodaj nowe puste zapytanie, wklej je i nazwij je HeaderID .

Zrobię ten sam proces dla podtabel. Skopiuję ten ciąg tekstowy, utworzę kolejne puste zapytanie i wkleję tę wartość. To będzie ciąg, którego będziemy używać, szukając szczegółowych wierszy.

Jeśli ten proces w jakiś sposób zmieni nagłówek którejkolwiek z tych tabel, wszystko, co muszę zrobić, to zmienić jeden z ciągów tekstowych, a plik znów będzie działał.

Naprawdę nie muszę zagłębiać się w kod M, aby wyszukać ten ciąg, którego szukamy. Możemy po prostu użyć tego jako parametru.

Włączmy ładowanie dla tych dwóch zapytań.

Tworzenie listy buforów w Microsoft Power Query

Pierwszą rzeczą, którą zrobię, będzie przekształcenie Kolumny 1 w listę, odwołując się do niej i ładując ją raz do pamięci. W ten sposób nie muszę wykonywać wielokrotnych wywołań do pliku.

Otworzę zaawansowany edytor i umieszczę go na samej górze. Gdy użyjesz interfejsu użytkownika do zbudowania kodu, będzie on odwoływał się do poprzedniego kroku.

Kiedy umieścisz krok bufora gdziekolwiek indziej w swoim kodzie i będziesz chciał wprowadzić modyfikację, później pomoże ci to wprowadzić zmiany w kroku, który tworzysz ręcznie.

Nazwę to BufferList i odwołam się do Column1. Aby załadować go do pamięci, dodam krok List.Buffer .

Oto moja zmienna na samej górze. Mogę się do niego odwoływać w kółko.

Pierwszą rzeczą, którą chcę ustalić, jest miejsce, w którym zaczynają się moje nagłówki, ponieważ potrzebuję klucza do przechowywania tych sekcji nagłówka i uzyskania jednej wartości dla wszystkich tych wierszy. Aby to zrobić, dodam niestandardową kolumnę i nazwię ją Header .

Napiszę, że jeśli Kolumna1 jest równa naszemu identyfikatorowi nagłówka, to chcę, aby mój numer indeksu był pusty.

W rezultacie znalazł tekst i zwrócił 5 i 23.

Potrzebuję tej wartości we wszystkich wierszach, więc muszę ją wypełnić. Możesz po prostu kliknąć prawym przyciskiem myszy, aby wypełnić, ale możesz także użyć bardzo prostej składni i dodać to na pasku formuły.

W tym przypadku dodałem Table.FillDown i w ciągu tekstowym zaznaczyłem, którą kolumnę chcemy wypełnić (Header).

Teraz wypełniliśmy to we wszystkich wierszach. Mamy klucz do wszystkich sekcji nagłówka i wszystkich sekcji wierszy, ponieważ wszystkie mają tę samą wartość.

Dzielenie nagłówków z wierszy

Następnym krokiem jest podzielenie nagłówków z wierszy. Dodam kolejną niestandardową kolumnę i nazwię ją Temp . Tym razem zrobimy coś bardziej skomplikowanego i wykorzystamy tę BufferList , którą stworzyłem wcześniej.

Będziemy używać kilku funkcji list, aby spojrzeć na każdą z pozycji i sprawdzić, czy istnieje dopasowanie do indeksu.

Zacznę od instrukcji if i użyję List.Contains , aby wyszukać określoną pozycję w BufferList i odwołać się do zapytania HeaderID .

Chcemy go znaleźć na całej długości pliku, a następnie zwrócić pozycję elementu na liście. Jeśli pasuje do indeksu, mamy dopasowanie dla tego konkretnego wiersza.

Następnie chcę zwrócić wartość identyfikującą nagłówek. W tym przypadku zwracam po prostu H. Skopiuję składnię, aby nie musieć pisać wszystkiego od nowa.

Musimy również zidentyfikować sekcję wiersza. Jeśli lista nie zawiera HeaderID , ale DetailID , to jesteśmy w sekcji wierszy.

Jeśli Column1 jest pustym ciągiem tekstowym, chcę, aby pozostał pusty . Jeśli tak nie jest, chcę, aby to było null .

Ta opozycja otrzymała wiersz nagłówka i zwróciła H, a następnie znalazła szczegółowy wiersz i zwróciła R. Następnie zwróciła 0 dla wszystkich elementów, które są współdzielone w tej sekcji wiersza.

Te spacje lub wartości zerowe są ważne, ponieważ umożliwiają wypełnienie. Wypełnij nie przesunie się po tych pustych komórkach, więc możemy je później wyeliminować.

Zrobimy to na pasku formuły i ponownie użyjemy Table.FillDown . Chce listy z nazwą kolumny, która jest naszą kolumną Temp .

Teraz mamy wartości H i R powtarzające się w całej tej kolumnie, co oznacza, że ​​możemy faktycznie podzielić nagłówki ze szczegółowych sekcji.

Możesz również wypełnić go z interfejsu użytkownika, jeśli nie chcesz pisać kodu. Możesz po prostu kliknąć prawym przyciskiem myszy i wybrać Fill , a następnie Down .

Usuwanie wartości zerowych i spacji w Microsoft Power Query

Teraz, gdy mamy to prawo, możemy wyeliminować rzeczy, których nie potrzebujemy. Wszystko, co ma wartość null lub zawiera puste miejsce, to wiersze, których nie potrzebujemy i musimy je usunąć. Możemy je wyeliminować poprzez filtrowanie.

Dzielenie sekcji

Gdy usuniemy te spacje i wartości zerowe, pozostanie nam wszystko, czego potrzebujemy. W tym momencie możemy po prostu podzielić sekcje. Możemy skupić się na wierszach nagłówka i wybrać je, ponieważ mają one osobne odstępy od wszystkich wierszy szczegółów (które również mają osobne odstępy).

Dodam nowy krok na pasku formuły, który pozwoli mi utworzyć kolejny filtr w tej samej kolumnie. W tym przypadku po prostu zachowam wszystkie sekcje nagłówka.

Teraz mam tutaj wszystkie te wiersze nagłówków.

Mogę wybrać Column1 , przejść do paska formuły, wybrać Split column , a następnie podzielić według pozycji.

Następnie poproś samo zapytanie o zasilanie, aby to rozgryźć. Zaproponuje kilka pozycji. Kliknij OK , aby zaakceptować te pozycje.

Jedyne, co nas interesuje z nagłówków, to pozycja i numer zlecenia .

Wewnątrz paska formuły mogę zmienić ich nazwy za pomocą Item i Job # . To uratuje mnie przed kolejnym krokiem zmiany nazwy kolumny.

Po tym kroku wszystko, co muszę zrobić, to wybrać Item , wybrać Job # i oczywiście wybrać nasz klucz nagłówka . Następnie usunę wszystkie pozostałe kolumny, ponieważ nie są mi już potrzebne.

To będzie wynik. Nadal musimy wyczyścić wartości i usunąć element tekstowy oraz myślniki. Wszystko, czego chcemy, to wartości pomiędzy.

Więc otwieramy to i odznaczamy myślniki i elementy.

Teraz wszystkie nagłówki są gotowe.

Musimy wykonać ten sam proces również dla DetailID . Muszę zmienić nazwę tych kroków, aby później łatwiej było mi do nich wrócić.

Powrócimy do początkowego zapytania, które rozpoczęliśmy. Zaczęliśmy od Filtrowanych wierszy w okienku Zastosowane kroki.

Skopiuję to i dodam do mojego filtra. Tym razem nie wybieram H, ale R.

Następnie wybiorę Kolumnę 1, przejdę do kolumny Podziel, podzielę według pozycji, a następnie sprawdzę, czy zapytanie mocy to rozgryzie.

To właśnie sugeruje Power Query. Spróbujmy tego.

To faktycznie wygląda całkiem nieźle. Nawet wszystkie rzędy rozdzielają się idealnie. Oczywiście jest dużo spacji, ponieważ mieliśmy to wcięcie.

Przycinanie ciągów tekstowych w Microsoft Power Query

Zaznaczę tę pierwszą kolumnę, a następnie naciśnij klawisze Down + Shift, aby wybrać kolumnę 1.10. Przejdź do Przekształć , wybierz Format , a następnie Przytnij . Przycinanie usunie tylko nadmiar spacji z przodu lub na końcu łańcucha, a nie pomiędzy.

Następnie możemy po prostu promować nagłówki, więc nie muszę wpisywać wszystkich nagłówków ani tytułów dla tych kolumn. W kroku podziału zmieniłem nazwy dwóch kolumn. Teraz, oczywiście, z 10 kolumnami, to trochę kłopotliwe.

Musimy także pozbyć się tych nadmiarowych wartości. Ponieważ mamy sumy, muszę użyć jednej z tych trzech ostatnich kolumn, ponieważ są to jedyne wiersze, które mają dodatkowe wartości gdzieś pomiędzy. Następnie odznaczymy te spacje, myślniki i teksty.

Następnie usunę niepotrzebne kolumny, więc pozostanie tylko tabela z nagłówkami i tylko szczegółami. Potrzebujemy klucza, aby ponownie połączyć te sekcje.

W tym celu możemy użyć auto-scalania, abyśmy mogli scalić tabelę ze sobą, aby ponownie połączyć te informacje. Na karcie Narzędzia główne wybierz pozycję Scal , a następnie wybierz pozycję Kolumna 5 i to samo zapytanie.

Zamiast AllDetails chcę, aby AllHeaders był moją początkową tabelą, z którą chcę się scalić.

Spowodowało to przywrócenie wszystkich informacji z tabeli nagłówków z pojedynczym wierszem dla każdej pozycji i każdego numeru zadania.

Użyliśmy klucza do połączenia z wierszami szczegółów. Jeśli przesunę się w bok w tym białym miejscu, zobaczymy podgląd wszystkich wierszy należących do nagłówka 5.

W tym miejscu usuniemy ostatnią kolumnę, a następnie zakończymy naprawianie mieszanej stałej szerokości kolumny w zapytaniu Microsoft Power Query.

Wniosek

W tym samouczku opracowaliśmy sposób rozwiązania problemów z mieszaną stałą szerokością kolumn za pomocą Microsoft Power Query. Jeśli podobały Ci się treści omówione w tym samouczku, nie zapomnij zasubskrybować kanału telewizyjnego LuckyTemplates.

Cały czas publikujemy ogromną ilość treści ode mnie i wielu twórców treści, z których wszyscy poświęcają się ulepszaniu sposobu, w jaki korzystasz z usługi LuckyTemplates i platformy Power Platform.

Melisa