CONCATENATEX в Power Query. Новата функция TEXTJOIN е страхотна. Можете ли да направите същото с Power Query? Да. Сега ти можеш.
Гледам видео
- Зрителят изтегля данни от система, където всеки елемент е разделен с Alt + Enter
- Бил: Защо правиш това? Преглед: Това е начинът, по който наследявам данните. Искам да го запазя така.
- Бил: Какво искате да направите с 40% от стойностите, които не са в таблицата? Зрител: Няма отговор
- Бил: Има сложен начин да се реши това, ако имате най-новите инструменти за Power Query.
- Вместо това, VBA макрос за решаването му - макросът трябва да работи чак до Excel 2007
- Вместо да правите VLOOKUP, направете серия от Find & Replace с VBA
Видео транскрипция
Научете Excel от, Подкаст Епизод 2151.
Наистина не знам как да нарека този. Ако се опитвам да привлека хората, които използват DAX, бих казал ConcatenateX в Power Query или просто хората, които използват обикновен Excel, но Office 365, бих казал TEXTJOIN в Power Query или, за да бъда напълно честен, това е супер сложен набор от стъпки в Power Query за активиране на супер лудо решение в Excel.
Хей. Добре дошли отново в netcast. Аз съм Бил Джелен. Е, вчера в епизод 2150 описах проблема. Някой изпрати в този файл, където системата му изтегля елементите, които са поръчка, с линейни емисии помежду им. С други думи, ALT + ENTER и вижте, WRAP TEXT е включен и те искат да направят VLOOKUP в тази LOOKUPTABLE за всеки от тези елементи. Аз съм като, какво? Защо правиш това? Но аз го покрих вчера. Нека просто се опитаме да разберем как да направим това.
Всъщност казах, добре, Power Query ще бъде най-добрият начин да направя това, но се запънах как да направя последната част. Казах, добре ли е, ако всеки елемент се озове на свой ред? Не, те трябва да се върнат в тази оригинална последователност. Аз съм като, това е ужасно, но в емисията ми в Twitter само миналата седмица Тим Родман, 27 септември: „Най-накрая прочетох тази книга“ - предполагам, че е PowerPivot Alchemy - „и вече получих желанието си за ConcatenateX ” Бях умен, когато направих това, като поисках PERHAPS ROMANX, но сигурно наистина исках ConcatenateX и затова Тим ми даде глава, че вече мога да го направя в Power BI.
И така, излязох при моите приятели, Роб Коли в Power Pivot Pro и Мигел Ескобар, и знаете ли, и двамата са автори на страхотни книги. Имам и двете книги, но тази функция е твърде нова, не е в нито една книга. Казах, хей, вие знаете ли как се прави това? И Мигел печели наградата, защото Мигел беше рано тази сутрин или късно снощи - не съм сигурен коя - и изпрати кода.
Добре, ето, ето плана в Power Query и този е толкова сложен. Никога не изписвам план в Power Query. Просто отивам да правя всички неща. Ще започна с оригиналните данни, добавете ИНДЕКС колона, за да можем да запазим елементите от поръчка заедно, РАЗДЕЛЕТЕ КОЛОНА НА РЯДОВЕ, като използвате LINEFEED. За втори или трети път в подкаста използвам тази нова функция. Колко готино е това. Имах втора ИНДЕКС колона, за да можем да сортираме елементите в оригиналната последователност и след това да запазим като връзка.
След това ще стигнем до таблицата LOOKUP, ще я направим таблица, заявка от таблица, ЗАПАЗВАНЕ КАТО ВРЪЗКА - това щеше да бъде най-лесната част точно там - и след това обединяване на тази заявка и тази заявка въз основа на елемент номер, всички елементи от лявата таблица, това е лявата таблица, съвпадаща от дясната, заменете nulls с номера на елемента. Все още сме във въздуха какво искаме да направим, когато нещо не е намерено по някаква причина. Задал съм този въпрос, но човекът, който е изпратил файла, не отговаря, така че просто ще го заменя с номера на артикула. Надяваме се, че правилното нещо е да добавите още елементи към LOOKUPTABLE, така че да няма неразкрити, но ето ни и след това ще сортираме по INDEX1 и INDEX2, така че по този начин,нещата се върнаха в правилната последователност и тогава това беше частта, която не можах да разбера как да го направя.
Ще се групираме по INDEX1, като правим еквивалента на TEXTJOIN или ConcatenateX с символ 10 като разделител, като агрегатор и, разбира се, това е най-трудната част, но това е наистина новата част тук в този набор от стъпки. Така че, ако разбирате какво TEXTJOIN прави или може да концептуализира какво би направил ConcatenateX, ние по същество правим това, използвайки този вид стъпка. Така че, добре. Така че, нека опитаме.
И така, ще започнем оттук. Ето нашите оригинални данни, има заглавие. И така, аз ще ФОРМАТИРАМ КАТО ТАБЛИЦА, CONTROL + T, МОЯТА МАСА ИМА ГЛАВИ, да, и тогава ще използваме Power Query. Сега съм в Excel 2016 Office 365, така че е тук в лявата част на раздела ДАННИ. Ако сте само в Excel 2016, а не в Office 365, той е в средата - ПОЛУЧАЙТЕ И ПРЕОБРАЗЕТЕ. Ако сте в Excel 2010 или 2013, това ще бъде свой собствен раздел, наречен Power Query, и ако нямате този раздел, ще трябва да изтеглите този раздел. Ако сте на Mac или Android или някоя от другите фалшиви версии на Excel, съжалявам, няма Power Query за вас. Вземете версия на Excel за Windows и опитайте.
Добре, така че ще направим Power Query ОТ ТАБЛИЦА, добре, и първото нещо, което ще направя, е ДАДАМ ИНДЕКСНА КОЛОНА и ще започна от 1. Добре , така че това по същество е ред 1, ред 2, ред 3, ред 4. Тогава ще изберем тази колона и в раздела ПРЕОБРАЗВАНЕ ще разделим колона, ЧРЕЗ ДЕЛИМЕТЪР, и те са успели да открийте, че това е LineFeed е разделителят. Харесва ми, че Power Query открива това. Сега, защо Excel, текст към колони, да, текст към колони не разбере какъв е разделителят? И всеки случай ще разделим на редове и ИЗПОЛЗВАНЕ НА СПЕЦИАЛЕН ХАРАКТЕР. Добре, така че всичко това е добре.
Сега гледайте какво се случва тук. Имаме 999 реда, но сега имаме много повече от това. И така, всеки елемент от този номер на поръчка вече е собствен ред. Сега, човекът, който е задал този въпрос, не иска той да бъде негов собствен ред, но ще трябва да го направим свой собствен ред, за да можем да направим присъединяването. Тук ще добавя нова колона INDEX. ДОБАВЕТЕ КОЛОНА, ИНДЕКС КОЛОНА, ОТ 1, и така имаме … това са по същество номерата на поръчките и тогава това са последователностите в поръчката, защото аз определих, че по-късно те ще бъдат в някакъв друг ред. Не знам на какъв ред преминават, но ето ни.
Добре де, НАЧАЛО, не бутон ЗАТИВАНЕ И НАТОВАРВАНЕ, а падащото меню ЗАТИВАНЕ И НАТОВАРВАНЕ и ЗАТВОРЕНЕ И ТОВАР. Не знам защо им отнема 10 секунди, за да покажат този диалогов прозорец за първи път. Ще създадем САМО СЪЗДАВАНЕ НА ВРЪЗКА. Щракнете върху OK. Красив. Това е ТАБЛИЦА1, ТАБЛИЦА1.
Сега ще отидем на нашия LOOKUPTABLE. LOOKUPTABLE ще бъде лесен за обработка. Ще форматираме това като таблица. CONTROL + T. Щракнете върху OK. DATA или POWER QUERY, ако сте в стара версия, ОТ ТАБЛИЦА. Това ще се нарича TABLE2. Нека го наречем LOOKUPTABLE. Перфектно. ЗАТИРАЙТЕ И НАТОВАРВАЙТЕ, ЗАТИВАЙТЕ И ТОВАРЯТЕ САМО СЪЗДАВАНЕ НА ВРЪЗКА.
Добре. Сега имаме нашите два бита тук и искам да ги обединя. И така, просто ще отидем на ново място и след това DATA, GET DATA, COMBINE QUERIES, ще направим MERGE, а таблицата вляво ще бъде TABLE1 - това са нашите първоначални данни - - и ще използваме този номер на ITEM и ще го оженим до LOOKUPTABLE и този номер на ITEM. Там наистина е неинтуитивно, трябва да щракнете върху ИЗДЕЛИЯТА и в двата случая, за да дефинирате какъв е ключът, и ВЪНШНО присъединяване, ВСИЧКО ОТ ПЪРВО, СЪОБЩЕНИЕ ОТ ВТОРО и, вижте, има 40% от тях, които липсват в ТАБЛИЦА ЗА СПРАВКИ. Това са всички фалшиви данни, но първоначалните данни също са липсвали 40% от LOOKUPTABLE. Наистина разочароващо. Добре. И така, тук е нашият ITEM номер, нашите 2 полета INDEX, а след това и нашата LOOKUPTABLE тук. Азще разширя това и ще поиска ОПИСАНИЕТО. Добре, виждате ли, че имаме куп нули тук.
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.
Сега, хей, това е точката, в която обикновено ви моля да отидете да купите моята книга, но днес нека вместо това ви помолим да отидете да купите книгата на Мигел. Мигел Ескобар и Кен Пулс написаха тази отлична книга на тема M Is For (DATA) MONKEY - най-добрата книга за Power Query. Отидете да проверите това.
Добре, завършете: днес е наистина дълъг епизод; имаме визуализатор, изтегля данни от система, където всеки елемент е разделен от ALT + ENTER и се опитваме да направим VLOOKUP за всеки отделен елемент; изгради решение днес, използвайки Power Query, включително инструмента за структурирана колона на извличане като; но това работи само в списък, а не в таблица, така че трябваше да използвам функцията TABLE.COLUMN, за да преобразувам таблицата в списък.
Е, хей. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2151.xlsm
