CONCATENATEX Power Queryssä. Uusi TEXTJOIN-toiminto on mahtava. Voitko tehdä saman asian Power Queryn kanssa? Joo. Nyt voit.
Katso video
- Katsoja lataa tietoja järjestelmästä, jossa kukin kohde erotetaan Alt + Enter-näppäimillä
- Bill: Miksi teet tämän? Katsoja: Näin perin tiedot. Haluan pitää sen sillä tavalla.
- Bill: Mitä haluat tehdä 40 prosentin arvojen kanssa, joita ei ole taulukossa? Katsoja: Ei vastausta
- Bill: On monimutkainen tapa ratkaista tämä, jos sinulla on uusimmat Power Query -työkalut.
- Sen sijaan VBA-makro sen ratkaisemiseksi - makron pitäisi toimia aina takaisin Excel 2007: een
- Tee VLOOKUP: n sijasta sarja Etsi ja korvaa VBA: lla
Videon transkriptio
Opi Excel, Podcast Episode 2151.
En todellakaan tiedä mitä kutsua tälle. Jos yritän houkutella DAXia käyttäviä ihmisiä, sanoisin ConcatenateX Power Queryssä tai vain ihmiset, jotka käyttävät tavallista Exceliä, mutta Office 365, sanoisin TEXTJOIN Power Queryssä, tai, ollakseni täysin rehellinen, se on erittäin monimutkainen vaihejoukko Power Query -ohjelmassa superhullun ratkaisun käyttöönottamiseksi Excelissä.
Hei. Tervetuloa takaisin netcastiin. Olen Bill Jelen. No, eilen jaksossa 2150 kuvasin ongelmaa. Joku lähetti tähän tiedostoon, jossa heidän järjestelmänsä lataa tilauksen kohteet, joiden välissä on rivinvaihtoa. Toisin sanoen, ALT + ENTER, ja katso, WRAP TEXT on päällä, ja he haluavat tehdä VLOOKUPin tähän LOOKUPTABLE-kappaleeseen jokaiselle kyseiselle kohteelle. Olen kuin mitä? Miksi teet tätä? Mutta kerroin siitä eilen. Yritetään vain selvittää, miten tämä tehdään.
Sanoin oikeastaan, että Power Query olisi paras tapa tehdä tämä, mutta sain järkyttyneen siitä, miten viimeinen osa tehdään. Sanoin, onko okei, jos jokainen kohde päätyy omalle rivilleen? Ei, heidän on palattava takaisin alkuperäiseen järjestykseen. Olen kuin, se on kamalaa, mutta viime viikolla Twitter-syötteessäni Tim Rodman, 27. syyskuuta: "Viimeinkin lukenut tämän kirjan" - luulen sen olevan PowerPivot Alchemy - "ja jo saanut ConcatenateX-toiveensa. ” Olin älykäs, kun tein tämän, pyysin PERHAPS ROMANXia, mutta luultavasti todella halusin ConcatenateX: n, joten Tim antoi minulle heads upin, että voin nyt tehdä sen Power BI: ssä.
Joten menin ystävieni, Rob Collien, Power Pivot Pron ja Miguel Escobarin luokse, ja tiedätkö, he ovat molemmat loistavien kirjojen kirjoittajia. Minulla on molemmat kirjat, mutta tämä ominaisuus on liian uusi, ei kummassakaan kirjassa. Sanoin, hei, tiedätkö kuinka tehdä tämä? Ja Miguel voittaa palkinnon, koska Miguel oli ylös tänä aamuna tai viime yönä myöhään - en ole varma kumpi - ja lähetti koodin.
Selvä, tässä on Power Queryn suunnitelma, ja tämä on niin monimutkainen. En koskaan kirjoita suunnitelmaa Power Queryyn. Menen vain tekemään kokonaisia asioita. Aloitan alkuperäisistä tiedoista, lisää INDEX-sarake, jotta voimme pitää tilauksen kohteet yhdessä, SPLIT COLUMN - ROWS käyttäen LINEFEED. Tämä on podcastin toinen tai kolmas kerta, kun olen käyttänyt tätä uutta ominaisuutta. Kuinka siistiä. Minulla oli toinen INDEX-sarake, jotta voimme lajitella kohteet alkuperäiseen järjestykseen ja sitten TALLENNA YHTEYDENÄ.
Sitten tulemme LOOKUP-taulukkoon, teemme siitä taulukon, kyselyn taulusta, TALLENNA YHTEYDENÄ - se oli siellä helpoin osa - ja sitten yhdistetään tämä kysely ja tämä kysely kohteen perusteella numero, kaikki kohteet vasemmasta taulukosta, tämä on vasen taulukko, joka sopii oikealta, korvaa nollat tuotenumerolla. Olemme edelleen ilmassa, mitä haluamme tehdä, kun jotain ei löydy jostain syystä. Olen esittänyt tämän kysymyksen, mutta tiedosto lähettänyt henkilö ei vastaa, joten aion vain korvata sen tuotenumerolla. Toivottavasti oikea tapa on lisätä lisää kohteita LOOKUPTABLE-tiedostoon, joten ei löydy yhtään löydettyä, mutta tässä me olemme, ja aiomme sitten lajitella INDEX1: n ja INDEX2: n mukaan, jotenasiat ovat palanneet oikeassa järjestyksessä, ja sitten se oli osa, jota en voinut selvittää miten.
Ryhmittelemme INDEX1: n mukaan tekemällä TEXTJOIN- tai ConcatenateX-ekvivalentin, jonka erottimena on merkki 10, aggregaattorina, ja tietysti tämä on se osa, joka on vaikea osa, mutta se on osa, joka on todella uusi täällä tämä vaihejoukko. Joten jos ymmärrät, mitä TEXTJOIN tekee tai pystyy käsittelemään, mitä ConcatenateX olisi tehnyt, teemme sen lähinnä tällaisella askeleella. Joten, okei. Joten, annamme sille kuvan.
Joten, aloitamme täällä. Tässä on alkuperäiset tiedot, sillä on otsikko. Joten aion muotoilla taulukon, CONTROL + T: n, taulukossa on otsikot, kyllä, ja sitten käytämme Power Queryä. Nyt olen Excel 2016 Office 365: ssä, joten se on täällä DATA-välilehden vasemmalla puolella. Jos olet vain suorassa Excel 2016: ssa, ei Office 365: ssä, se on keskellä - GET & TRANSFORM. Jos olet Excel 2010: ssä tai 2013: ssa, se tulee olemaan oma välilehti täällä nimeltä Power Query, ja jos sinulla ei ole tätä välilehteä, sinun on ladattava kyseinen välilehti. Jos käytössäsi on Mac tai Android tai jokin muu Excelin väärennetty versio, anteeksi, ei virtakyselyä sinulle. Hanki Windows-versio Excelistä ja kokeile tätä.
Selvä, siis aiomme tehdä virtakyselyn PÖYTÄSTÄ, okei, ja ensimmäinen asia, jonka teen, on LISÄÄ HAKEMISTOSARAKE ja aloitan alusta 1. Hyvä on. , joten tämä on pohjimmiltaan järjestys 1, järjestys 2, järjestys 3, järjestys 4. Sitten valitsemme tämän sarakkeen ja Muunna-välilehdellä siirrymme SPLIT COLUMN, BY DELIMITER, ja he pystyivät havaitse, että se on LineFeed on erotin. Rakastan, että Power Query havaitsee tämän. Miksi Excel, teksti sarakkeisiin, joo, teksti sarakkeisiin ei selvitä mikä erotin on? Ja jokainen tapahtuma aiomme jakautua RIVIKSI ja KÄYTTÄÄ ERIKOISMERKITTÄ. Selvä, joten kaikki se on hyvää.
Katso nyt mitä täällä tapahtuu. Rivejä on 999, mutta nyt niitä on paljon enemmän. Joten jokainen tilausnumeron kohde on nyt oma rivi. Nyt tämän kysymyksen esittänyt henkilö ei halua sen olevan oma rivi, mutta meidän on tehtävä siitä oma rivi, jotta voimme tehdä liittymisen. Aion lisätä tähän uuden INDEX-sarakkeen. LISÄÄ SARAKE, HAKKOSARAKKEET, ALK. 1, ja niin meillä on… nämä ovat lähinnä järjestysnumeroita ja sitten nämä ovat järjestyksessä olevaa järjestystä, koska olen päättänyt, että myöhemmin nämä ovat jossakin muussa järjestyksessä. En tiedä mihin järjestykseen he vaihtavat, mutta tässä olemme.
Selvä, HOME, ei SULJE & LADAA-painiketta, mutta avattava SULJE & LADAA-painike ja SULJE & LADAA. En tiedä, miksi kestää 10 sekuntia, ennen kuin he näyttävät tämän valintaikkunan ensimmäisen kerran. Aiomme luoda VAIN YHTEYDEN. Napsauta OK. Kaunis. Joten se on taulukko1, taulukko1.
Nyt aiomme mennä LOOKUPTABLE. LOOKUPTABLE on helppo käsitellä. Alustamme tämän taulukoksi. CONTROL + T. Napsauta OK. DATA tai POWER QUERY, jos olet vanhassa versiossa, PÖYTÄSTÄ. Tätä kutsutaan taulukoksi 2. Kutsutaan sitä LOOKUPTABLE. Täydellinen. SULJE JA LASI, SULJE JA LADAA, LOA AINOASTA YHTEYS.
Hyvä on. Nyt meillä on kaksi bittiämme täällä ja haluan yhdistää nämä kaksi. Joten menemme vain uuteen paikkaan ja sitten TIEDOT, HANKI DATA, YHDISTÄ kyselyitä, teemme YHDISTYKSEN, ja vasemmalla oleva taulukko tulee olemaan TAULUKKO1 - se on alkuperäiset tietomme - - ja aiomme käyttää tätä ITEM-numeroa ja mennä naimisiin sen kanssa LOOKUPTABLE- ja ITEM-numeroon asti. Se ei todellakaan ole intuitiivista, joten sinun on napsautettava TUOTTEITA molemmissa tapauksissa määritelläksesi mikä avain on, ja OUTER-liittymä, KAIKKI ENSIMMÄISESTÄ, VASTAANOTTAMISESTA TOISESTA, ja näette, että näistä puuttuu 40% HAKUTAULUKKOON. Tämä on kaikki väärennettyjä tietoja, mutta myös LOOKUPTABLE-tiedostosta puuttui 40% alkuperäisistä tiedoista. Todella turhauttavaa. Hyvä on. Joten tässä on ITEM-numero, 2 INDEX-kenttää ja sitten LOOKUPTABLE. Minä 'aion laajentaa sitä ja pyytää KUVAUS. Selvä, näet, että meillä on joukko nollia.
Selvä, joten aiomme tehdä ehdollisen sarakkeen. Ehdollinen sarake sanoo katsovan tätä saraketta. Jos se on = nolla, tuo tämä arvo yli, muuten käytä arvoa, joka on kyseisessä sarakkeessa. Joten tässä, LISÄÄ SARAKE -kohdassa, teemme EHDOLLINEN SARAKE - mukava pieni käyttöliittymä, joka opastaa meidät tämän läpi - jos LOOKUPTABLEDESCRIPTION YHDEN MUKAAN, niin haluamme käyttää saraketta TUOTTEISTA, muuten haluamme käyttää Sarake nimeltä LOOKUPDESCRIPTION, ok. Napsauta OK ja siellä olemme. Mukautettu -sarakkeemme sisältää joko uuden arvon LOOKUPTABLE-arvosta tai alkuperäisen arvon, jos sitä ei löydy. Tässä vaiheessa voimme napsauttaa hiiren kakkospainikkeella ja sanoa, että haluamme POISTA tämän sarakkeen. Se oli väliaikainen sarake, se oli auttajapylväs. Nyt kun meillä on mitä tarvitsemme, emme tarvitse sitä saraketta enää, ja itse asiassa tässä vaiheessaEn myöskään tarvitse tätä saraketta. Joten voin napsauttaa hiiren kakkospainikkeella ja POISTAa kyseisen sarakkeen. Hyvä on. Nyt meillä on tietomme täällä. Haluan lajitella sen alkuperäisen hakemiston mukaan. Joten, Lajittele nouseva. Se saa tietomme oikeaan järjestykseen, ja nyt kun se on lajiteltu, voin todella napsauttaa hiiren kakkospainikkeella ja POISTAA kyseisen sarakkeen.
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.
Hei, tämä on kohta, jossa pyydän yleensä sinua ostamaan kirjani, mutta tänään pyydetään sen sijaan sinua ostamaan Miguelin kirja. Miguel Escobar ja Ken Puls kirjoittivat tämän erinomaisen kirjan M Is For (DATA) MONKEY -lehdestä - paras teos Power Querystä. Mene katsomaan sitä.
Selvä, päätä: tänään on todella pitkä jakso; meillä on katsoja, lataa tietoja järjestelmästä, jossa kukin kohde on erotettu ALT + ENTER: llä ja yritämme tehdä VLOOKUP: n jokaiselle yksittäiselle tuotteelle; rakensi ratkaisun tänään käyttämällä Power Queryä, joka sisältää strukturoidun sarakkeen työkalun otteesta mutta se toimii vain luettelossa, ei taulukossa, joten minun piti käyttää TABLE.COLUMN-funktiota muunnettaessa taulukko luetteloksi.
No hei. Haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2151.xlsm
