Jak dynamicznie łączyć kolumny w tabeli dodatku Power Query

W tym samouczku przeanalizujemy interesujące pytanie, które zostało zadane na dotyczące sposobu dynamicznego scalania zmieniającej się liczby kolumn w tabeli dodatku Power Query. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Ten scenariusz dotyczy eksportów nieprzetworzonych danych, które powodują rozlewanie danych do nieznanej liczby sąsiednich kolumn. Gdy spojrzysz na przykład, zobaczysz, że opis został przeniesiony do Kolumny 4, a Opis 2 został podzielony na dwie sąsiednie kolumny. Ale nie zawsze tak będzie. W następnym cyklu ta liczba kolumn może się zmienić.

Zanim przejdziemy do rozwiązania, przyjrzyjmy się kodowi M generowanemu przez dodatek Power Query podczas scalania kolumn.

Spis treści

• Kod M tabeli Power Query
• Scalanie zmieniającej się liczby kolumn w tabeli dodatku Power Query
• Wniosek

Kod M tabeli Power Query

Istnieje kilka sposobów scalania kolumn. Jednym z nich jest wybranie zarówno kolumny opisu, jak i naciśnięcie klawisza Shift lub Control, a następnie wybranie również kolumny 4. Następnie kliknij prawym przyciskiem myszy i wybierz Scal kolumny .

Lub na karcie Przekształć wybierz scalanie kolumn . Pojawi się to okno dialogowe, w którym możesz wybrać Separator.

W wyskakującym okienku wybierz Tab i możesz wprowadzić nową nazwę kolumny. W tym przypadku nazwałem go Opis.

Na pasku formuły widzimy kod M utworzony w tym kroku transformacji.

Jeśli pasek formuły nie jest widoczny na ekranie, przejdź do karty Widok i włącz ją.

Tak więc funkcja, której dodatek Power Query używa do scalania kolumn, to Table.CombinedColumns . Pierwszym parametrem, który pobiera, jest tabela. Ta tabela jest zwracana przez poprzedni krok w naszym kodzie, więc w zastosowanych krokach zobaczysz, że nazywa się to Source .

Następnie zakodował na stałe nazwy kolumn na liście. Tutaj widzisz inicjatory listy, a między wartościami w tekście, opis nazwy kolumny i kolumnę nazwy kolumny 4. Następnie wywołuje inną funkcję M, aby połączyć wartości tekstowe w tych kolumnach.

I na koniec przekazuje nową nazwę kolumny jako tekst, abyśmy mogli to dostosować.

Jeśli więc chcemy, aby ta funkcja dynamicznie scalała zmieniającą się liczbę kolumn, będziemy musieli zmienić wartość tego drugiego parametru, który zawiera teraz listę zakodowanych na stałe nazw kolumn.

Scalanie zmieniającej się liczby kolumn w tabeli dodatku Power Query

Wrócę do mojego zapytania o surowe dane i jeśli przyjrzymy się bliżej nazwom kolumn, zobaczymy, że każda z kolumn wycieku jest anonimowa. Nie mają nazw własnych, ale wszystkie zaczynają się od kolumny tekstowej, po której następuje liczba. Zobaczmy, czy możemy to wykorzystać. Najpierw utworzę odniesienie , klikając tutaj prawym przyciskiem myszy i wybierając Odniesienie .

Aby uzyskać nazwy kolumn na pasku formuły, mogę dodać Table.ColumnNames . Ta funkcja zwraca listę zawierającą wszystkie nazwy kolumn z tej tabeli. Zamieńmy to z powrotem w tabelę, klikając Do tabeli .

W tym wyskakującym oknie kliknij OK.

Następnie na karcie Dodaj kolumnę wybiorę Format , a następnie Przytnij .

To nie jest transformacja, którą chcę wykonać, ale generuje ona dla mnie większość kodu M. Wszystko, co muszę zrobić, to zastąpić tę funkcję przycinania własną logiką .

Tak więc w pasku formuły, zamiast Text.Trim , możemy powiedzieć if Text.StartsWith , a następnie wskazać naszą kolumnę 1 , a ja natychmiast to skopiuję. Chce tego tekstu, którego szukamy, więc to będzie Kolumna. Więc jeśli zaczyna się od kolumny tekstowej, chcemy „null” – dla wszystkiego innego chcemy tego, co znajduje się w kolumnie 1. Możemy również zmienić nazwę tej kolumny, więc w pasku formuły zamiast Trim nazwijmy ją GroupColumn .

Teraz wszystko, co musimy zrobić, to wypełnić je tymi wartościami. Klikam więc prawym przyciskiem myszy nagłówek, wybieram Fill i Down .

Tak więc następnym razem, gdy pojawią się dane i zmieni się liczba kolumn, ta grupa automatycznie je pobierze.

Zmieńmy nazwę tego zapytania, nazwę go grupami kolumn.

Upewnijmy się, że jego ładowanie zostało wyłączone, ponieważ jest to tylko zapytanie pomocnicze.

Mogę odwołać się do tego zapytania pomocniczego i zmienię jego nazwę później. Zostawmy to na razie. Jeśli pamiętasz, zaczęliśmy od scalenia dwóch kolumn i w ten sposób utworzyliśmy zakodowaną na stałe listę z nazwami kolumn , ale teraz możemy filtrować według opisu w naszej GroupColumn .

Jeśli więc wykonamy ten filtr w opisie w kolumnie 1, otrzymamy kolumny spełniające te kryteria.

Jest to jednak tabela, a nie lista. Musimy wyodrębnić wszystko, co pozostaje w tej pierwszej kolumnie, ponieważ musimy uwzględnić te kolumny w naszej operacji scalania. Aby to zrobić, możemy kliknąć prawym przyciskiem myszy nagłówek kolumny 1 i wybrać opcję Drąż w dół .

I teraz otrzymujemy listę. Nadajmy więc temu zapytaniu odpowiednią nazwę ( ListDescr ) i sprawdźmy, czy jest wyłączone z ładowania.

Teraz możemy wrócić do naszego zapytania Result i zastąpić ten drugi argument tutaj naszą listą dynamiczną. Więc tutaj możemy odwołać się do naszego ListDescr .

Powtórzmy to również dla Opisu 2. Przejdę do mojego zapytania ColumnGroups. Utworzę kolejne odwołanie i wybiorę potrzebne kolumny. Następnie przefiltruję opis 2, który zwróci te trzy kolumny. Kliknę prawym przyciskiem myszy mój nagłówek i wybiorę opcję Uszczegółowienie, a także zmienię nazwę tego zapytania ( ListDescr2 ).

Następnie wrócę do mojego zapytania wynikowego, wybierz kolumnę Opis2 i naciśnij klawisz Shift lub Control, aby wybrać również następną kolumnę (kolumna2). Klikam prawym przyciskiem myszy nagłówek i wybieram Scal kolumny .

W wyskakującym okienku wybiorę Tab jako separator i nazwę to Opis2.

Po prostu zmienię nazwę kolumny tutaj na pasku formuły. Zmienię również tę zakodowaną na stałe listę z listy, którą właśnie utworzyliśmy. A teraz, jak widać, dotyczy to również kolumny 8, którą wcześniej wykluczyliśmy.

Jak scalać zapytania w usłudze LuckyTemplates
Parametry zapytań usługi LuckyTemplates: Optymalizacja tabel
Sortowanie kolumn tabeli dat w usłudze LuckyTemplates

Wniosek

Na tym blogu pokazałem, jak używać kodów M w dodatku Power Query do dynamicznego scalania kolumn. Nie jest to trudne, o ile wiesz, jak działa Power Query i jakich kodów M użyć.

Mam nadzieję, że ci się podobało. Sprawdź poniższe linki, aby uzyskać więcej powiązanych treści.

Wszystkiego najlepszego!

Melisa