CONCATENATEX w dodatku Power Query. Nowa funkcja TEXTJOIN jest niesamowita. Czy możesz zrobić to samo z dodatkiem Power Query? Tak. Teraz możesz.
Obejrzyj wideo
- Przeglądarka pobiera dane z systemu, w którym każdy element jest oddzielony klawiszami Alt + Enter
- Bill: Dlaczego to robisz? Przeglądający: w ten sposób dziedziczę dane. Chcę, żeby tak zostało.
- Bill: Co chcesz zrobić z 40% wartości, których nie ma w tabeli? Przeglądający: Brak odpowiedzi
- Bill: Jeśli masz najnowsze narzędzia Power Query, można to rozwiązać w skomplikowany sposób.
- Zamiast tego do rozwiązania tego problemu służy makro VBA - makro powinno działać aż do programu Excel 2007
- Zamiast wykonywać WYSZUKAJ.PIONOWO, wykonaj serię funkcji Znajdź i zamień za pomocą VBA
Transkrypcja wideo
Naucz się programu Excel z, podcast, odcinek 2151.
Naprawdę nie wiem, jak to nazwać. Jeśli chcę przyciągnąć ludzi używających języka DAX, powiedziałbym, że ConcatenateX w dodatku Power Query lub tylko osoby, które używają zwykłego programu Excel, ale Office 365, powiedziałbym TEXTJOIN w dodatku Power Query lub, mówiąc całkowicie szczerze, to bardzo złożony zestaw kroków w dodatku Power Query, aby włączyć niezwykle szalone rozwiązanie w programie Excel.
Hej. Witamy ponownie w netcast. Jestem Bill Jelen. Cóż, wczoraj w odcinku 2150 opisałem problem. Ktoś wysłał ten plik, w którym jego system pobiera pozycje, które są zamówieniami z kolejkami między nimi. Innymi słowy, ALT + ENTER i zobacz, WRAP TEXT jest włączony i chcą wykonać WYSZUKAJ.PIONOWO w tej WYSZUKIWARCE dla każdego z tych elementów. Jestem jak, co? Dlaczego to robisz? Ale omówiłem to wczoraj. Spróbujmy po prostu dowiedzieć się, jak to zrobić.
Właściwie powiedziałem, cóż, dodatek Power Query byłby najlepszym sposobem na zrobienie tego, ale nie mogłem się doczekać, jak zrobić ostatnią część. Powiedziałem, czy to w porządku, jeśli każdy element kończy się we własnym rzędzie? Nie, muszą powrócić w tej oryginalnej sekwencji. Myślę, że to okropne, ale na moim kanale na Twitterze w zeszłym tygodniu Tim Rodman, 27 września: „Wreszcie czytam tę książkę” - zgaduję, że to PowerPivot Alchemy - „i już ma życzenie ConcatenateX. ” Robiłem to mądrze, prosząc o PERHAPS ROMANX, ale prawdopodobnie naprawdę chciałem ConcatenateX, więc Tim dał mi znać, że teraz mogę to zrobić w Power BI.
Poszedłem więc do moich przyjaciół, Roba Collie z Power Pivot Pro i Miguela Escobara, i wiesz, obaj są autorami świetnych książek. Mam obie te książki, ale ta funkcja jest zbyt nowa, nie ma jej w żadnej z nich. Powiedziałem, hej, czy wiecie, jak to zrobić? Miguel zdobywa nagrodę, ponieważ Miguel wstał wcześnie rano lub późno wczoraj wieczorem - nie jestem pewien, która z nich - i wysłał kod.
W porządku, oto plan w dodatku Power Query, a ten jest tak skomplikowany. Nigdy nie piszę planu w dodatku Power Query. Po prostu idę zrobić wszystko. Zacznę od oryginalnych danych, dodam kolumnę INDEKS, abyśmy mogli zachować razem pozycje z zamówienia, PODZIEL KOLUMNĘ na WIERSZE za pomocą LINEFEED. To już drugi lub trzeci podcast, w którym użyłem tej nowej funkcji. Jakie to jest świetne. Miałem drugą kolumnę INDEKS, abyśmy mogli posortować elementy w oryginalnej kolejności, a następnie ZAPISZ JAKO POŁĄCZENIE.
Następnie przejdziemy do tabeli WYSZUKAJ, uczynimy z niej tabelę, zapytanie z tabeli, ZAPISZ JAKO POŁĄCZENIE - to miała być najłatwiejsza część właśnie tam - a następnie scalimy to zapytanie i to zapytanie na podstawie elementu numer, wszystkie pozycje z lewej tabeli, to jest lewa tabela, pasujące od prawej, zamień wartości puste na numer pozycji. Nadal jesteśmy w powietrzu, zastanawiając się, co chcemy zrobić, gdy z jakiegoś powodu czegoś nie znaleziono. Zadałem to pytanie, ale osoba, która wysłała plik, nie odpowiada, więc zastąpię je numerem przedmiotu. Mamy nadzieję, że należy dodać więcej elementów do TABELI WYSZUKIWANIA, aby nie było żadnych nie znalezionych, ale oto jesteśmy, a następnie posortujemy według INDEX1 i INDEX2, więc w ten sposóbwszystko wróciło we właściwej kolejności i wtedy nie mogłem wymyślić, jak to zrobić.
Będziemy grupować według INDEX1, wykonując odpowiednik TEXTJOIN lub ConcatenateX ze znakiem 10 jako separatorem, jako agregatorem i oczywiście jest to część, która jest najtrudniejsza, ale jest to część, która jest naprawdę nowa tutaj w ten zestaw kroków. Tak więc, jeśli rozumiesz, co robi TEXTJOIN lub możesz konceptualizować to, co zrobiłby ConcatenateX, zasadniczo robimy to za pomocą tego rodzaju kroku. Więc w porządku. Więc spróbujmy.
Więc zaczniemy tutaj. Oto nasze oryginalne dane, ma nagłówek. Tak więc zamierzam FORMAT AS TABLE, CONTROL + T, MY TABLE HAS HEADERS, tak, a następnie użyjemy dodatku Power Query. Teraz jestem w Excel 2016 Office 365, więc jest tutaj, po lewej stronie zakładki DANE. Jeśli korzystasz tylko z programu Excel 2016, a nie Office 365, to jest w środku - POBIERZ I PRZEKSZTAŁCENIE. Jeśli korzystasz z programu Excel 2010 lub 2013, będzie to osobna karta o nazwie Power Query, a jeśli nie masz tej karty, będziesz musiał pobrać tę kartę. Jeśli korzystasz z komputera Mac lub systemu Android lub dowolnej innej fałszywej wersji programu Excel, przepraszamy, brak dodatku Power Query. Pobierz wersję programu Excel dla systemu Windows i wypróbuj ją.
W porządku, więc zrobimy zapytanie Power Query FROM A TABLE, dobrze, a pierwszą rzeczą, którą zrobię, jest DODAJ KOLUMNĘ INDEKSU i zacznę OD 1. W porządku , więc to jest w zasadzie porządek 1, porządek 2, porządek 3, porządek 4. Następnie wybieramy tę kolumnę i na karcie TRANSFORMACJA zamierzamy SPLIT COLUMN, BY DELIMITER, i byli w stanie wykryć, że jest to LineFeed jest ogranicznikiem. Podoba mi się, że dodatek Power Query to wykrywa. Dlaczego Excel, tekst do kolumn, tak, tekst do kolumn nie określa, co to jest separator? I każde wystąpienie PODZIELIMY NA WIERSZE I UŻYWAJĄC SPECJALNYCH ZNAKÓW. W porządku, więc to wszystko dobrze.
Now watch what happens here. We have 999 rows but now we have far more than that. So, every item in that order number is now its own row. Now, the person who asked this question doesn't want it to be its own row but we're going to have to make it be its own row so we can do the join. I'm going to add a new INDEX column here. ADD COLUMN, INDEX COLUMN, FROM 1, and so we have… these are essentially the order numbers and then these are the sequence within the order because I've determined that, later, these are going to be in some other order. I don't know what order they switch to but here we are.
Alright, so, HOME, not the CLOSE & LOAD button but the CLOSE & LOAD drop-down, and CLOSE & LOAD TO. I don't know why it takes 10 seconds for them to display this dialog box the first time. We're going to ONLY CREATE CONNECTION. Click OK. Beautiful. So that's TABLE1, TABLE1.
Now, we're going to go to our LOOKUPTABLE. LOOKUPTABLE is going to be easy to process. We're going to format this as a table. CONTROL+T. Click OK. DATA, or POWER QUERY if you’re in an old version, FROM TABLE. This is going to be called TABLE2. Let's call it LOOKUPTABLE. Perfect. CLOSE & LOAD, CLOSE & LOAD TO, ONLY CREATE CONNECTION.
Alright. Now, we have our two bits over here and I want to merge those two. So, we're just going to go to a new spot and then DATA, GET DATA, COMBINE QUERIES, we're going to do a MERGE, and the table on the left is going to be TABLE1 -- that's our original data -- and we're going to use this ITEM number and we're going to marry that up to the LOOKUPTABLE and that ITEM number. It’s really non-intuitive there you have to click on the ITEMS in both cases to define what the key is, and an OUTER join, ALL FROM FIRST, MATCHING FROM SECOND, and, see, there's 40% of these that are missing from the LOOKUPTABLE. This is all fake data but the original data had 40% missing from the LOOKUPTABLE as well. Really kind of frustrating. Alright. So, here's our ITEM number, our 2 INDEX fields, and then our LOOKUPTABLE here. I'm going to EXPAND that and ask for the DESCRIPTION. Alright, you see we have a bunch of nulls here.
Alright, so, we're going to do a conditional column. Conditional column’s going to say look at this column. If it's = to null, then bring this value over, otherwise, use the value that's in that column. So here, under ADD COLUMN, we’ll do CONDITIONAL COLUMN -- nice little UI that'll walk us through this -- if the LOOKUPTABLEDESCRIPTION EQUALS NULL, then we want to use a COLUMN here of ITEMS, otherwise, we want to use the COLUMN called LOOKUPDESCRIPTION, alright. Click OK, and there we are. There's our CUSTOM column with either the new value from the LOOKUPTABLE or the original value if it's not found. At this point, we can right click and say that we want to REMOVE this column. It was a temporary column, it was a helper column. Now that we have what we need, we don't need that column anymore, and actually, at this point, I don't need this column anymore either. So, I can right click and REMOVE that column. Alright. Now, we have our data here. I want to sort it by the original INDEX. So, SORT ASCENDING. That gets our data into the right sequence, and now that it's sorted, I can actually right click and REMOVE that column.
Alright. Now we’re at the point where, for every item, each order number -- so, this is order number 1, let's say -- I want to have these 4 items separated by a LineFeed character. Now, what I was hoping to be able to do was to come here to TRASNFORM. Instead, if we wanted to GROUP BY and that there'd be some magic here in the GROUP BY, I would say I'm going to concatenate or textjoin all those things, but it doesn't work, alright?
So, here's the set of steps that are new to me that allow this to happen. First thing we want to do is we're going to create a brand new column. That column is just going to be called a TABLECOLUMN and we're going to take ALL ROWS and click OK. Okay. So, when we look inside at this table, we see that we have 2 columns -- one called INDEX and one called CUSTOM -- and we have to remember that name there, alright, and this table unfortunately does not work with STRUCTURED COLUMN. See, EXTRACT VALUES is grayed out. So, this doesn't work with a table. It has to work with a list. I have to convert this table to a list, and this is the part I couldn't figure out and the part that Miguel filled in for me.
So, I'm going to create a CUSTOM COLUMN here and I'm going to call it a LISTCOLUMN and we're going to use a function called TABLE.COLUMN and the table is the thing called TABLECOLUMN, and then which column in there is the thing called CUSTOM. Click OK. Alright, and now these are, instead of a table, it is a list. We're home-free now. TRANSFORM, STRUCTURED COLUMN. I'm going to EXTRACT VALUES. I'm going to create a CUSTOM delimiter USING SPECIAL CHARACTERS, INSERT SPECIAL CHARACTER, LINE FEED, and click OK, and it gives me what I'm looking for. So here’s my original order number. The TABLE, we don’t need anymore, right click and REMOVE that, and we now have our original data using the LOOKUPTABLE where we need it, alright? So, I can right-click and REMOVE this, alright, and then finally, HOME, just straight CLOSE & LOAD, which brings it back into a table in Excel. (=Table.Column((TableColumn),“Custom”))
Alright, but it doesn't look like it worked, does it? That's because, by default, this table does not have WRAP TEXT turned on. So, HOME, WRAP TEXT, and we now have our new data doing the equivalent of a VLOOKUP for each item in the list, and when an item is not found, the original item number is still there, so someone can go piece that back together.
Now, the beautiful thing with Power Query is that while it took us some time to get this set up the first time, the next time we download this list, we just copy it here, and we can even edit something. So, let’s change one. So, MANGO, 4954, we’ll take that 7036 and change it to 4954. Alright, so, now the underlying data has changed, all we have to do is come here to this and click the REFRESH which will refresh all of these items, and we get here to SHEET11 and that second item has changed to a MANGO, alright? It’ll take you some time to set this up once but, once you get it set up, it's just a simple matter of refreshing the data and Power Query will go through all those steps.
Teraz, hej, to jest punkt, w którym zwykle proszę cię, abyś kupił moją książkę, ale dzisiaj zamiast tego poprośmy, abyś kupił książkę Miguela. Miguel Escobar i Ken Puls napisali tę doskonałą książkę o M Is For (DATA) MONKEY - najlepszej dostępnej książce w Power Query. Idź to sprawdzić.
W porządku, podsumuj: dzisiaj jest naprawdę długi odcinek; mamy przeglądarkę, pobiera dane z systemu, w którym każdy element jest oddzielony przez ALT + ENTER i próbujemy wykonać WYSZUKAJ.PIONOWO dla każdego elementu; zbudował dziś rozwiązanie przy użyciu dodatku Power Query, w tym narzędzia do kolumny strukturalnej wyodrębniania jako; ale to działa tylko na liście, a nie na tabeli, więc musiałem użyć funkcji TABLE.COLUMN, aby przekonwertować tabelę na listę.
Więc hej. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2151.xlsm
