ЦОНЦАТЕНАТЕКС у Повер Куери-у. Нова функција ТЕКСТЈОИН је сјајна. Можете ли учинити исто са Повер Куери-ом? Да. Сада можете.
Погледајте видео
- Прегледник преузима податке из система где је свака ставка одвојена Алт + Ентер
- Билл: Зашто то радиш? Гледалац: Тако наследим податке. Желим да тако и остане.
- Билл: Шта желите да урадите са 40% вредности које нису у табели? Гледалац: Нема одговора
- Билл: Постоји сложен начин да се ово реши ако имате најновије Повер Куери алате.
- Уместо тога, ВБА макро за решавање - макро би требало да ради све до Екцел 2007
- Уместо да радите ВЛООКУП, направите серију Пронађи и замени са ВБА
Видео транскрипт
Научите Екцел из, Подцаст епизода 2151.
Заиста не знам како да назовем ову. Ако покушавам да привучем људе који користе ДАКС, рекао бих ЦонцатенатеКс у Повер Куери-у, или само људе који користе уобичајени Екцел, али Оффице 365, рекао бих ТЕКСТЈОИН у Повер Куери-у или, да будем потпуно искрен, то је супер сложен скуп корака у Повер Куери-у који омогућава изузетно лудо решење у програму Екцел.
Хеј! Добродошли назад на нетцаст. Ја сам Билл Јелен. Па, јуче у епизоди 2150, описао сам проблем. Неко је послао ову датотеку тамо где њихов систем преузима ставке које су редоследом, а линијски додаци између њих. Другим речима, АЛТ + ЕНТЕР и видите, ВРАП ТЕКСТ је укључен и они желе да изврше ВЛООКУП у овај ЛООКУПТАБЛЕ за сваку од тих ставки. Ја сам као, шта? Зашто ово радиш? Али то сам покривао јуче. Покушајмо само да схватимо како то да урадимо.
Заправо сам рекао, па, Повер Куери би био најбољи начин да се ово уради, али запамтио сам како да урадим последњи део. Рекао сам, да ли је у реду ако свака ставка заврши у свом реду? Не, они се морају вратити у овом оригиналном низу. Мислим да је то ужасно, али на свом Твиттер фиду прошле недеље, Тим Родман, 27. септембра: „Коначно сам прочитао ову књигу“ - претпостављам да је то ПоверПивот Алцхеми - „и већ имам своју ЦонцатенатеКс жељу. ” Био сам паметан кад сам то радио, тражећи ПЕРХАПС РОМАНКС, али вероватно сам заиста желео ЦонцатенатеКс, и тако ми је Тим рекао да то сада могу да радим у Повер БИ-у.
Па сам изашао код својих пријатеља, Роба Цоллиеја из Повер Пивот Проа и Мигуела Есцобара, и, знате, обојица су аутори сјајних књига. Имам обе ове књиге, али ова функција је превише нова, ни у једној књизи. Рекао сам, хеј, знате ли људи како се то ради? А Мигуел осваја награду јер је Мигуел устао рано јутрос или касно синоћ - нисам сигуран у коју - и послао шифру.
У реду, па, ево плана у Повер Куери-у, а овај је тако сложен. Никада не записујем план у Повер Куери-у. Једноставно идем да радим све ствари. Започећу са оригиналним подацима, додајте ступац ИНДЕКС да бисмо заједно могли да задржимо ставке из наруџбине, ПОДЕЛИТИ КОЛОНУ у РЕДОВЕ помоћу ЛИНЕФЕЕД-а. Ово је други или трећи пут у подцасту да користим ову нову функцију. Како је то кул. Имао сам другу ИНДЕКС колону како бисмо могли да сортирамо ставке у првобитни низ, а затим СПАСИМО КАО ВЕЗУ.
Затим ћемо доћи до табеле ЛООКУП, направити табелу, упит из табеле, САЧУВАТИ КАО ВЕЗУ - то ће бити најлакши део управо тамо - а затим спојити овај упит и овај упит на основу ставке броја, све ставке из леве табеле, ово је лева табела, подударање с десне, нуле замените бројем ставке. Још увек смо у ваздуху шта желимо да радимо када нешто из неког разлога није пронађено. Поставио сам ово питање, али особа која је послала датотеку не одговара, па ћу је заменити бројем предмета. Надамо се да је права ствар да додате још ставки у ПОГЛЕДАЈУЋУ ТАБЛУ како не би било непоуздања, али ево нас, а онда ћемо сортирати по ИНДЕКС1 и ИНДЕКС2, па на тај начин,ствари су се вратиле у правом редоследу и тада је ово био део који нисам могао да смислим како да то учиним.
Груписаћемо се по ИНДЕКС1 радећи еквивалент ТЕКСТЈОИН или ЦонцатенатеКс са знаком 10 као сепаратором, као агрегатором, и, наравно, ово је најтежи део, али то је део који је овде заиста нов овај сет корака. Дакле, ако разумете шта ТЕКСТЈОИН ради или може концептуализовати оно што би ЦонцатенатеКс урадио, у основи то радимо користећи овакав корак. Па, у реду. Па, хајде да покушамо.
Дакле, почећемо овде. Ево наших оригиналних података, има наслов. Дакле, форматираћу ТАБЕЛУ, ЦОНТРОЛ + Т, МОЈ ТАБЕЛА ИМА ГЛАВЕ, да, а онда ћемо користити Повер Куери. Сада сам у програму Екцел 2016 Оффице 365, тако да је овде на левом делу картице ДАТА. Ако сте само у Екцел 2016-у, а не у Оффице 365, то је у средини - ГЕТ & ТРАНСФОРМ. Ако сте у програму Екцел 2010 или 2013, то ће овде бити сопствена картица под називом Повер Куери, а ако је немате, морат ћете је преузети. Ако користите Мац или Андроид или неку другу лажну верзију програма Екцел, извините, нема Повер Куери-а за вас. Набавите Виндовс верзију програма Екцел и пробајте ово.
У реду, дакле, урадићемо Повер Куери из табеле, у реду, и прва ствар коју ћу да урадим је да додам индексну колону и да започнем од 1. У реду , дакле, ово је у суштини ред 1, налог 2, налог 3, ред 4. Тада ћемо изабрати ову колону и на картици ТРАНСФОРМУЈЕМО ПОДЕЛИТИ КОЛОНУ, ДЕЛИМЕРОМ, и они су могли открити да је ЛинеФеед граничник. Свиђа ми се што Повер Куери ово открива. Е сад, зашто Екцел, текст у колоне, да, текст у колоне не схвати шта је граничник? И сваки случај ћемо ПОДЕЛИТИ У РЕДОВЕ, И КОРИСТЕЋИ ПОСЕБНИ КАРАКТЕР. У реду, па све је то добро.
Пази шта се овде дешава. Имамо 999 редова, али сада имамо и више од тога. Дакле, свака ставка под тим редним бројем сада је свој ред. Сада, особа која је поставила ово питање не жели да то буде свој ред, али мораћемо да постави свој ред да бисмо могли да извршимо спајање. Овде ћу додати нову ИНДЕКС колону. ДОДАЈ КОЛОНУ, ИНДЕКС КОЛОНУ, ОД 1, и тако имамо … ово су у суштини бројеви редоследа, а затим су то редоследи унутар налога, јер сам утврдио да ће касније бити у неком другом редоследу. Не знам у који редослед прелазе, али ту смо.
У реду, дакле, ХОМЕ, не дугме ЗАТВОРИ & УТОВАРИ, већ падајући мени ЗАТВОРИ & УТОВАРИ и ЗАТВОРИ & УТОВАРИ. Не знам зашто им треба 10 секунди да први пут прикажу овај дијалог. Идемо САМО ДО СТВАРАЊА ВЕЗЕ. Кликните ОК. Лепа. Дакле, то је ТАБЕЛА1, ТАБЕЛА1.
Сада ћемо ићи на наш ПОГЛЕДАЈУЋИ СТО. ЛООКУПТАБЛЕ ће бити лако обрадити. Ово ћемо форматирати као табелу. ЦОНТРОЛ + Т. Кликните ОК. ПОДАЦИ или ПОВЕР КУЕРИ ако имате стару верзију, ОД ТАБЛЕ. Ово ће се звати ТАБЕЛА2. Назовимо то ЛООКУПТАБЛЕ. Савршено. ЗАТВОРИ И УТОВАРИ, ЗАТВОРИ И УТОВАРИ, САМО СТВАРИ ВЕЗУ.
У реду. Сада имамо своја два бита овде и желим да спојим та два. Дакле, идемо само на ново место, а затим ДАТА, ГЕТ ДАТА, ЦОМБИНЕ КУЕРИЕС, урадићемо МЕРГЕ, а табела са леве стране биће ТАБЕЛА1 - то су наши оригинални подаци - - и користићемо овај ИТЕМ број и венчат ћемо га до ЛООКУПТАБЛЕ и тог ИТЕМ броја. Тамо је заиста неинтуитивно, у оба случаја морате кликнути на АРТИКЛЕ да бисте дефинисали шта је кључ и ВАЊСКО придруживање, СВЕ ОД ПРВОГ, ОДГОВАРАЊЕ ОД ДРУГОГ, и, видите, 40% ових недостаје у ЛООКУПТАБЛЕ. Ово су сви лажни подаци, али оригиналним подацима је такође недостајало 40% у ЛООКУПТАБЛЕ. Стварно фрустрирајуће. У реду. Дакле, овде је наш ИТЕМ број, наша 2 ИНДЕКС поља, а затим наш ЛООКУПТАБЛЕ овде. Ја 'Идем то ПРОШИРИТИ и тражити ОПИС. У реду, видите да имамо гомилу нула овде.
У реду, урадићемо условну колону. Условна колона ће рећи погледајте ову колону. Ако је = нулл, пренесите ову вредност у супротном, користите вредност која се налази у тој колони. Дакле, овде, под ДОДАЈЕМ КОЛОНУ, урадићемо КОНДИЦИОНАЛНУ КОЛОНУ - лепо мало корисничко сучеље које ће нас провести кроз ово - ако је ЛООКУПТАБЛЕДЕСЦРИПТИОН ЈЕДНАКО НУЛЛ, онда овде желимо да користимо КОЛОНУ СТАВА, у супротном, желимо да користимо КОЛОНА која се зове ЛООКУПДЕСЦРИПТИОН, у реду. Кликните ОК и ето нас. Постоји наша ЦУСТОМ колона са новом вредношћу ЛООКУПТАБЛЕ или оригиналном вредношћу ако није пронађена. У овом тренутку можемо десним тастером кликнути и рећи да желимо УКЛОНИТИ ову колону. Била је то привремена колона, помоћна колона. Сад кад имамо оно што нам треба, та колона нам више није потребна, и заправо, у овом тренутку,Ни мени више није потребна ова колумна. Дакле, могу десним тастером да кликнем и УКЛОНИМ ту колону. У реду. Сада имамо своје податке овде. Желим да је сортирам по оригиналном ИНДЕКСУ. Дакле, РАСПОРЕДАЈУЋЕ. То доводи наше податке у праву секвенцу, а сада када су сортирани, стварно могу кликнути десним тастером миша и УКЛОНИТИ ту колону.
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.
Ето, хеј, ово је тачка у којој обично тражим да одеш да купиш моју књигу, али данас, уместо тога, замолимо те да одеш да купиш Мигуелову књигу. Мигуел Есцобар и Кен Пулс написали су ову изврсну књигу о М Ис Фор (ДАТА) МОНКЕИ - најбољој књизи о Повер Куери-у која постоји. Иди провери то.
У реду, заврши: данас је заиста дуга епизода; имамо приказивач, преузима податке из система где је свака ставка одвојена АЛТ + ЕНТЕР и покушавамо да направимо ВЛООКУП за сваку појединачну ставку; је данас изградио решење користећи Повер Куери, укључујући алатку за структуриране колоне екстракта ас; али то ради само на листи, а не на табели, па сам морао да користим функцију ТАБЛЕ.ЦОЛУМН да претворим табелу у листу.
Добро Хеј. Желим да вам захвалим што сте навратили. Видимо се следећи пут за још једно емитовање од.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2151.клсм
