CONCATENATEX у Power Query. Нова функція TEXTJOIN надзвичайна. Чи можете ви зробити те саме з Power Query? Так. Тепер ти можеш.
Переглянути відео
- Переглядач завантажує дані із системи, де кожен елемент розділений Alt + Enter
- Білл: Чому ти це робиш? Переглядач: Це те, як я успадковую дані. Я хочу зберегти так.
- Білл: Що ви хочете зробити із 40% значень, яких немає в таблиці? Глядач: відповіді немає
- Білл: Існує складний спосіб вирішити цю проблему, якщо у вас найновіші інструменти Power Query.
- Натомість макрос VBA для його вирішення - макрос повинен працювати аж до Excel 2007
- Замість того, щоб робити VLOOKUP, зробіть серію Знайти та замінити VBA
Стенограма відео
Дізнайтеся Excel з, Підкаст Епізод 2151.
Я справді не знаю, як цього назвати. Якщо я намагаюся залучити людей, які використовують DAX, я б сказав ConcatenateX у Power Query або просто людей, які використовують звичайний Excel, але Office 365, я б сказав TEXTJOIN у Power Query, або, чесно кажучи, це надзвичайно складний набір кроків у Power Query для забезпечення надмірно божевільного рішення в Excel.
Привіт Ласкаво просимо до мережі. Я Білл Джелен. Ну, вчора в епізоді 2150 я описав проблему. Хтось надсилає цей файл, де їх система завантажує елементи, які є замовленням, з подачами ліній між ними. Іншими словами, ALT + ENTER, і побачите, WRAP TEXT увімкнено, і вони хочуть зробити ПЕРЕГЛЯД в цей ШЛЯХ для кожного з цих елементів. Я люблю, що? Чому ви це робите? Але я про це розповів учора. Давайте просто спробуємо з’ясувати, як це зробити.
Я насправді сказав: ну, Power Query був би найкращим способом зробити це, але я заплутався, як зробити останню частину. Я сказав, чи добре, якщо кожен предмет опиняється у своєму рядку? Ні, вони повинні повернутися в цій початковій послідовності. Мені здається, це жахливо, але на моїй стрічці у Твіттері минулого тижня Тім Родман, 27 вересня: "Нарешті прочитав цю книгу", - я здогадуюсь, це алхімія PowerPivot - "і вже отримав своє бажання ConcatenateX. " Коли я це робив, я був розумним, просячи PERHAPS ROMANX, але я, мабуть, справді хотів ConcatenateX, і тому Тім підказав мені, що тепер я можу це робити в Power BI.
Тож я пішов до своїх друзів, Роб Коллі з Power Pivot Pro та Мігеля Ескобара, і, знаєте, вони обоє є авторами чудових книг. У мене є обидві ці книги, але ця функція занадто нова, не в жодній книзі. Я сказав, привіт, ви, хлопці, знаєте, як це зробити? А Мігель виграє приз, бо Мігель встав рано вранці або пізно вчора ввечері - я не впевнений, який саме - і надіслав код.
Гаразд, отже, ось план у Power Query, і цей настільки складний. Я ніколи не виписую план у Power Query. Я просто йду робити всі справи. Я збираюся розпочати з вихідних даних, додати стовпець INDEX, щоб ми могли тримати елементи з замовлення разом, РОЗДІЛИТИ СТОЛБЦУ на РЯДКИ за допомогою LINEFEED. Вже другий чи третій раз у подкасті я використовую цю нову функцію. Як це круто. У мене був другий стовпець INDEX, щоб ми могли сортувати елементи у вихідній послідовності, а потім ЗБЕРЕГТИ ЯК ПІДКЛЮЧЕННЯ.
Потім ми підійдемо до таблиці LOOKUP, зробимо її таблицею, запитом з таблиці, ЗБЕРЕГТИ ЯК ПІДКЛЮЧЕННЯ - це було найпростішою частиною саме там - а потім об’єднаємо цей запит і цей запит на основі елемента номер, усі елементи з лівої таблиці, це ліва таблиця, що відповідає правій, замініть нулі на номер елемента. Ми все ще в повітрі щодо того, що ми хочемо робити, коли чогось не вдається знайти з якихось причин. Я задав це запитання, але особа, яка надіслала файл, не відповідає, тому я просто заміню його номером елемента. Сподіваємось, правильно, що потрібно зробити, це додати більше елементів до ПЕРЕГЛЯДУ, щоб не було не знайдених, але ось ми тут, а потім ми будемо сортувати за 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 буде легко обробити. Ми відформатуємо це як таблицю. CONTROL + T. Клацніть OK. ДАНІ або POWER QUERY, якщо ви використовуєте стару версію, FROM TABLE. Це буде називатися TABLE2. Назвемо це LOOKUPTABLE. Ідеально ЗАКРИТИ І ЗАВАНТАЖИТИ, ЗАКРИТИ І ЗАВАНТАЖИТИ ТІЛЬКИ СТВОРИТИ ПІДКЛЮЧЕННЯ.
Гаразд Тепер у нас є два біти, і я хочу об’єднати ці два. Отже, ми просто перейдемо до нового місця, а потім ДАНІ, ОТРИМАТИ ДАНІ, КОМБІНУЙТЕ ЗАПИТИ, ми зробимо ЗЛИЧЕННЯ, а таблиця зліва буде ТАБЛИЦЕ1 - це наші вихідні дані - - і ми збираємось використовувати цей номер позиції, і ми збираємось одружувати його до LOOKUPTABLE та номера ITEM. Це насправді не інтуїтивно зрозуміло, там ви повинні натиснути на ПУНКТИ в обох випадках, щоб визначити, в чому полягає ключ, і ВНЕШНЄ приєднання, ВСЕ З ПЕРШОГО, ЗБІГАЮЧЕ З ДРУГОГО, і, бачите, 40% з них відсутні в ПОШУК. Це всі підроблені дані, але вихідні дані також не містили 40% у LOOKUPTABLE. Дійсно, це засмучує. Гаразд Отже, ось наш ПУНКТ, наші 2 ПОЛЯ ІНДЕКСУ, а потім наш ПЕРЕГЛЯД тут. Яя збираюся РОЗШИРИТИ це і попросити ОПИС. Гаразд, ти бачиш, у нас тут купа нулів.
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.
Зараз, привіт, це той момент, коли я зазвичай прошу вас піти купити мою книгу, але сьогодні, замість цього, попросимо вас піти купити книгу Мігеля. Мігель Ескобар і Кен Пулс написали цю чудову книгу на тему "М Є ДЛЯ (ДАНІ)" МАВПИ - найкраща книга з Power Query. Іди перевір це.
Гаразд, завершіть: сьогодні справді довгий епізод; у нас є програма перегляду, завантажує дані із системи, де кожен елемент розділений ALT + ENTER, і ми намагаємось зробити ПЕРЕГЛЯД для кожного окремого елемента; сьогодні побудував рішення за допомогою Power Query, включаючи інструмент структурованих стовпців екстракту as; але це працює лише зі списком, а не з таблицею, тому мені довелося використовувати функцію TABLE.COLUMN для перетворення таблиці в список.
Ну, привіт. Я хочу подякувати вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2151.xlsm
