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.
Привет. Добро пожаловать в сетевую трансляцию. Я Билл Джелен. Ну вот вчера в серии 2150 описал проблему. Кто-то отправил этот файл, где его система загружает элементы, которые являются заказом с переводом строки между ними. Другими словами, ALT + ENTER и видите, что WRAP TEXT включен, и они хотят выполнить VLOOKUP в этой LOOKUPTABLE для каждого из этих элементов. Мне нравится, что? Почему вы это делаете? Но я рассказал об этом вчера. Давайте просто попробуем разобраться, как это сделать.
Я на самом деле сказал, что лучше всего это сделать с помощью Power Query, но я не понимал, как сделать последнюю часть. Я сказал, нормально, если каждый элемент попадает в свою строку? Нет, они должны вернуться в исходную последовательность. Я подумал, это ужасно, но в моем твиттере на прошлой неделе Тим Родман, 27 сентября: «Наконец-то прочитаю эту книгу» - я полагаю, это PowerPivot Alchemy - «и уже получил свое желание ConcatenateX. ” Когда я это делал, я был умником, прося PERHAPS ROMANX, но я, вероятно, действительно хотел ConcatenateX, и поэтому Тим предупредил меня, что теперь я могу сделать это в Power BI.
Итак, я пошел к своим друзьям, Робу Колли из Power Pivot Pro и Мигелю Эскобару, и, как вы знаете, они оба являются авторами замечательных книг. У меня есть обе эти книги, но эта функция слишком нова ни в одной из них. Я сказал, а вы, ребята, знаете, как это сделать? И Мигель выигрывает приз, потому что Мигель встал рано утром или поздно вечером - я не уверен, какой именно - и отправил код.
Хорошо, вот план в Power Query, и этот такой сложный. Я никогда не пишу план в Power Query. Я просто делаю все. Я собираюсь начать с исходных данных, добавить столбец ИНДЕКС, чтобы мы могли хранить элементы заказа вместе, РАЗДЕЛЕНИЕ СТОЛБЦА на СТРОКИ, используя LINEFEED. Я уже второй или третий раз в подкасте использовал эту новую функцию. Как это круто. У меня был второй столбец ИНДЕКС, чтобы мы могли отсортировать элементы в исходной последовательности, а затем СОХРАНИТЬ КАК СОЕДИНЕНИЕ.
Затем мы перейдем к таблице LOOKUP, сделаем ее таблицей, запросом из таблицы, SAVE AS CONNECTION - это будет самая простая часть прямо здесь - а затем объединим этот запрос и этот запрос на основе элемента число, все элементы из левой таблицы, это левая таблица, сопоставление справа, замените нули на номер элемента. Мы все еще не уверены в том, что хотим делать, когда по какой-то причине что-то не нашли. Я задал этот вопрос, но человек, приславший файл, не отвечает, поэтому я просто заменю его номером позиции. Надеюсь, правильнее всего будет добавить больше элементов в 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 строк, но теперь их гораздо больше. Итак, каждый элемент в этом порядковом номере теперь является отдельной строкой. Теперь человек, задавший этот вопрос, не хочет, чтобы это была отдельная строка, но нам придется сделать ее отдельной строкой, чтобы мы могли выполнить соединение. Я собираюсь добавить сюда новый столбец ИНДЕКС. ДОБАВИТЬ COLUMN, INDEX COLUMN, FROM 1, и поэтому у нас есть … это, по сути, порядковые номера, а затем последовательность в порядке, потому что я определил, что позже они будут в другом порядке. Не знаю, в каком порядке они переходят, но вот мы здесь.
Хорошо, итак, HOME, а не кнопку ЗАКРЫТЬ и ЗАГРУЗИТЬ, а раскрывающийся список ЗАКРЫТЬ и ЗАГРУЗИТЬ и ЗАКРЫТЬ И ЗАГРУЗИТЬ В. Я не знаю, почему им требуется 10 секунд, чтобы отобразить это диалоговое окно в первый раз. Мы будем СОЗДАТЬ ТОЛЬКО СОЕДИНЕНИЕ. Щелкните ОК. Прекрасный. Итак, это ТАБЛИЦА1, ТАБЛИЦА1.
Теперь мы перейдем к нашей ТАБЛИЦЕ. LOOKUPTABLE будет легко обрабатывать. Мы собираемся отформатировать это как таблицу. КОНТРОЛЬ + Т. Щелкните ОК. DATA или POWER QUERY, если вы используете старую версию, FROM TABLE. Он будет называться ТАБЛИЦА 2. Назовем его LOOKUPTABLE. Отлично. ЗАКРЫТЬ И НАГРУЗИТЬ, ЗАКРЫТЬ И ЗАГРУЗИТЬ ТОЛЬКО СОЗДАТЬ СОЕДИНЕНИЕ.
Хорошо. Теперь у нас есть два бита, и я хочу их объединить. Итак, мы просто перейдем в новое место, а затем DATA, GET DATA, COMBINE QUERIES, мы собираемся выполнить MERGE, и таблица слева будет TABLE1 - это наши исходные данные - - и мы собираемся использовать этот номер ПУНКТА, и мы собираемся объединить его с ТАБЛИЦЕЙ ПРОСМОТРА и этим номером ПУНКТА. Это действительно неинтуитивно, вы должны щелкнуть ПУНКТЫ в обоих случаях, чтобы определить, что такое ключ, и ВНЕШНЕЕ соединение, ВСЕ С ПЕРВОГО, СОГЛАСОВАНИЕ СО ВТОРОЙ, и, видите ли, 40% из них отсутствуют СПРАВОЧНАЯ ТАБЛИЦА. Это все поддельные данные, но исходные данные также на 40% отсутствовали в LOOKUPTABLE. Действительно неприятно. Хорошо. Итак, вот наш номер ПУНКТА, наши 2 поля ИНДЕКС, а затем наша ПРОСМОТР. Я'm собираюсь РАСКРЫТЬ это и попросить ОПИСАНИЕ. Хорошо, как видите, у нас здесь куча нулей.
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, и мы пытаемся выполнить ВПР для каждого отдельного элемента; сегодня построили решение, используя Power Query, включая инструмент извлечения структурированных столбцов как; но это работает только со списком, а не с таблицей, поэтому мне пришлось использовать функцию TABLE.COLUMN для преобразования таблицы в список.
Ну привет. Я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2151.xlsm
