CONCATENATEX Power Query. Jaunā TEXTJOIN funkcija ir lieliska. Vai jūs varat darīt to pašu ar Power Query? Jā. Tagad Tu vari.
Skatīties video
- Skatītājs lejupielādē datus no sistēmas, kurā katrs elements tiek atdalīts ar Alt + Enter
- Bils: Kāpēc jūs to darāt? Skatītājs: tā es mantoju datus. Es gribu to saglabāt tādā veidā.
- Bils: Ko jūs vēlaties darīt ar 40% vērtībām, kas nav tabulā? Skatītājs: nav atbildes
- Bils: Ir sarežģīts veids, kā to atrisināt, ja jums ir jaunākie Power Query rīki.
- Tā vietā VBA makro, lai to atrisinātu - makro jādarbojas līdz pat Excel 2007
- Tā vietā, lai veiktu VLOOKUP, veiciet virkni Atrast un aizstāt ar VBA
Video atšifrējums
Uzziniet programmu Excel, Podcast epizode 2151.
Es tiešām nezinu, kā šo saukt. Ja es cenšos piesaistīt cilvēkus, kuri lieto DAX, es teiktu, ka Power Query ir ConcatenateX, vai tikai tos, kuri izmanto parasto Excel, bet Office 365, es Power Query teiktu: TEXTJOIN, vai, godīgi sakot, tas ir super sarežģīts darbību kopums programmā Power Query, lai programmā Excel iespējotu ārkārtīgi neprātīgu risinājumu.
Hei. Laipni lūdzam atpakaļ netcast. Es esmu Bils Jelens. Nu, vakar 2150. sērijā es aprakstīju problēmu. Kāds nosūtīja šajā failā, kur viņu sistēma lejupielādē pasūtījuma objektus, starp kuriem ir rindiņu padeves. Citiem vārdiem sakot, ALT + ENTER un redzi, WRAP TEXT ir ieslēgts, un viņi vēlas veikt VLOOKUP šajā LOOKUPTABLE katram no šiem vienumiem. Es esmu, kā? Kāpēc jūs darāt to? Bet es to vakar apskatīju. Izmēģināsim un izdomāsim, kā to izdarīt.
Es faktiski teicu: labi, Power Query būtu labākais veids, kā to izdarīt, bet es uzzināju, kā izdarīt pēdējo daļu. Es teicu, vai ir labi, ja katrs vienums nonāk savā rindā? Nē, viņiem ir jāatgriežas šajā sākotnējā secībā. Man patīk, ka tas ir briesmīgi, bet, manā Twitter plūsmā tikai pagājušajā nedēļā, Tims Rodmens, 27. septembris: “Beidzot izlasīju šo grāmatu,” - es domāju, ka tā ir PowerPivot Alchemy - “, un es jau saņēmu viņa vēlmi“ ConcatenateX ”. ” Kad es to darīju, es biju gudrs, lūdzot PERHAPS ROMANX, bet es, iespējams, patiešām vēlējos ConcatenateX, un tāpēc Tims man deva galvu, ka es to tagad varu darīt Power BI.
Tātad, es devos pie saviem draugiem, Robu Koliju pie Power Pivot Pro un Migelu Eskobaru, un, jūs zināt, viņi abi ir lielisku grāmatu autori. Man ir abas šīs grāmatas, taču šī funkcija ir pārāk jauna, ne vienā, ne otrā. Es teicu, hei, vai jūs, puiši, zināt, kā to izdarīt? Un Migels iegūst balvu tāpēc, ka Migels bija agri šorīt vai vēlu vakarā - es neesmu pārliecināts, kurš - un nosūtīja kodu.
Labi, lūk, šeit ir Power Query plāns, un šis ir tik sarežģīts. Es nekad neizrakstu plānu Power Query. Es vienkārši eju darīt visas lietas. Es sākšu ar sākotnējiem datiem, pievienojiet INDEX kolonnu, lai mēs varētu saglabāt vienumus no pasūtījuma, SPLIT COLUMN to ROWS, izmantojot LINEFEED. Šī ir otrā vai trešā reize podkāstā, kad izmantoju šo jauno funkciju. Cik tas ir forši. Man bija otra INDEX kolonna, lai mēs varētu kārtot vienumus sākotnējā secībā un pēc tam Saglabāt kā savienojumu.
Tad mēs nonāksim pie tabulas LOOKUP, izveidosim to par tabulu, vaicājumu no tabulas, SAVE AS CONNECTION - tas tur būs visvienkāršākā daļa - un pēc tam apvienosim šo vaicājumu un šo vaicājumu, pamatojoties uz vienumu numurs, visi vienumi no kreisās tabulas, šī ir kreisā tabula, kas atbilst no labās puses, aizstājiet nulles ar preces numuru. Mēs joprojām esam gaisā, ko mēs vēlamies darīt, ja kaut kāda iemesla dēļ kaut kas nav atrasts. Esmu uzdevis šo jautājumu, bet persona, kas nosūtīja failu, neatbild, tāpēc es to vienkārši nomainīšu ar preces numuru. Cerams, ka pareizais veids ir pievienot vairāk vienumu LOOKUPTABLE, lai nebūtu neviena atrasta, bet šeit mēs esam, un tad mēs kārtosim pēc INDEX1 un INDEX2, tādā veidā,lietas atgriežas pareizajā secībā, un tad šī bija tā daļa, kuru es nevarēju saprast, kā to izdarīt.
Mēs grupēsimies pēc INDEX1, veicot TEXTJOIN vai ConcatenateX ekvivalentu ar rakstzīmi 10 kā atdalītāju kā apkopotāju, un, protams, šī ir tā daļa, kas ir grūtā daļa, bet tā ir tā daļa, kas šeit ir patiešām jauna šo darbību kopu. Tātad, ja jūs saprotat, ko TEXTJOIN dara vai var konceptualizēt to, ko ConcatenateX būtu darījis, mēs to būtībā darām, izmantojot šāda veida darbību. Tātad, labi. Tātad, pieņemsim to nošaut.
Tātad, mēs sāksim šeit. Šeit ir mūsu sākotnējie dati, ir virsraksts. Tātad, es formulēšu TABULU, CONTROL + T, MANAM GALDAI IR GALVENAS, jā, un tad mēs izmantosim Power Query. Tagad es atrodos programmā Excel 2016 Office 365, tāpēc tas atrodas šeit cilnes DATA kreisajā daļā. Ja jūs vienkārši izmantojat Excel 2016, nevis Office 365, tas ir pa vidu - GET & TRANSFORM. Ja izmantojat programmu Excel 2010 vai 2013, tā būs sava cilne šeit ar nosaukumu Power Query, un, ja šīs cilnes jums nav, šī cilne būs jālejupielādē. Ja izmantojat Mac vai Android vai kādu citu Excel viltotu versiju, atvainojiet, jums nav Power Query. Iegūstiet Excel versiju Windows un izmēģiniet to.
Labi, tātad, mēs veiksim strāvas vaicājumu NO GALDA, labi, un pirmā lieta, ko es darīšu, ir PIEVIENOT RĀDĪTĀJU AILI un sākšu no 1. Labi , tātad, tas būtībā ir 1., 2., 3., 3., 4. kārtas. Tad mēs izvēlēsimies šo kolonnu un cilnē PĀRVEIDOŠANA dosimies uz SPLIT COLUMN, BY DELIMITER, un viņi varēja noteikt, ka tas ir LineFeed ir norobežotājs. Man patīk, ka Power Query to atklāj. Kāpēc Excel, teksts kolonnās, jā, teksts kolonnās neizprot, kas ir atdalītājs? Un katru gadījumu mēs sadalīsim rindās un izmantosim īpatnības. Labi, tāpēc tas viss ir labi.
Tagad skatieties, kas šeit notiek. Mums ir 999 rindas, bet tagad mums ir daudz vairāk. Tātad katrs šī pasūtījuma numura vienums tagad ir sava rinda. Tagad persona, kas uzdeva šo jautājumu, nevēlas, lai tā būtu sava rinda, bet mums tā būs jāpadara par savu rindu, lai mēs varētu veikt savienojumu. Es šeit pievienošu jaunu INDEX kolonnu. PIEVIENOT SLEJU, RĀDĪTĀJU SLEJU, NO 1, un tāpēc mums ir … tie būtībā ir kārtas numuri, un pēc tam tie ir secība secībā, jo es esmu noteicis, ka vēlāk tie būs citā secībā. Es nezinu, kādā secībā viņi pāriet, bet šeit mēs esam.
Labi, tātad, MĀJAS, nevis pogu AIZVĒRT UN IELĀDĒT, bet nolaižamo izvēlni AIZVĒRT UN IELĀDĒT un AIZVĒRT UN IELĀDĒT. Es nezinu, kāpēc paiet 10 sekundes, līdz viņi pirmo reizi parāda šo dialoglodziņu. Mēs veidosim TIKAI SAVIENOJUMU. Noklikšķiniet uz Labi. Skaists. Tātad tas ir TABULA1, TABULA1.
Tagad mēs ejam uz mūsu LOOKUPTABLE. LOOKUPTABLE būs viegli apstrādājama. Mēs to formatēsim kā tabulu. CONTROL + T. Noklikšķiniet uz Labi. DATI vai POWER QUERY, ja izmantojat vecāku versiju, FROM TABLE. To sauks TABLE2. Sauksim to par LOOKUPTABLE. Lieliski. AIZVĒRT UN IEKRĀVĒT, AIZVĒRT UN IEKRĀVĒT TIKAI SAVIENOJUMU.
Labi. Tagad mums šeit ir divi biti, un es vēlos tos abus apvienot. Tātad, mēs vienkārši ejam uz jaunu vietu un pēc tam DATI, IEGŪT DATUS, APVIENOT JAUTĀJUMUS, mēs saplūdīsim, un kreisajā pusē esošā tabula būs TABULA1 - tie ir mūsu sākotnējie dati - un mēs izmantosim šo ITEM numuru un precēsim to līdz LOOKUPTABLE un šim ITEM numuram. Tas tiešām nav intuitīvi, tāpēc abos gadījumos jums ir jānoklikšķina uz ITEMS, lai definētu, kas ir atslēga, un OUTER pievienoties, VISIEM PIRMAJIEM, SASKAŅOJAMIEM NO OTRO, un, redziet, 40% no tiem trūkst MEKLĒJAMS. Šie visi ir viltoti dati, taču sākotnējos datos arī LOOKUPTABLE trūka 40%. Patiešām tāda veida nomākta. Labi. Tātad, šeit ir mūsu ITEM numurs, mūsu 2 INDEX lauki un pēc tam šeit LOOKUPTABLE. EsEs gatavojos to PAPLAŠINĀT un lūgt APRAKSTU. Labi, jūs redzat, ka mums šeit ir virkne nulles.
Labi, tāpēc mēs veiksim nosacītu kolonnu. Nosacījuma slejā būs jāskatās uz šo kolonnu. Ja tas ir = līdz null, tad pārnesiet šo vērtību pāri, pretējā gadījumā izmantojiet vērtību, kas atrodas šajā kolonnā. Tātad šeit, zem PIEVIENOT SLEJU, mēs veiksim NOSACĪJUMU SLEJU - jauks mazs lietotāja interfeiss, kas mūs izstaigās - ja LOOKUPTABLEDESCRIPTION EQUALS NULL, tad mēs šeit vēlamies izmantot SLEJU no ITEMS, pretējā gadījumā mēs vēlamies izmantot kolonna sauc LOOKUPDESCRIPTION, labi. Noklikšķiniet uz Labi, un mēs esam. Šeit ir mūsu sleja CUSTOM ar jauno vērtību no LOOKUPTABLE vai sākotnējo vērtību, ja tā nav atrasta. Šajā brīdī mēs varam noklikšķināt ar peles labo pogu un teikt, ka mēs vēlamies NOŅEMT šo kolonnu. Tā bija pagaidu kolonna, tā bija palīgkolonna. Tagad, kad mums ir tas, kas mums nepieciešams, šī kolonna mums vairs nav vajadzīga, un faktiski šajā brīdīArī šī kolonna man vairs nav vajadzīga. Tātad, es varu ar peles labo pogu noklikšķināt un NOŅEMT šo kolonnu. Labi. Tagad mums ir mūsu dati. Es vēlos to kārtot pēc sākotnējā INDEX. Tātad, KĀRTOT PĀRCELŠANOS. Tādējādi mūsu dati nonāk pareizajā secībā, un tagad, kad tie ir sakārtoti, es faktiski varu noklikšķināt ar peles labo pogu un NOŅEMT šo kolonnu.
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.
Tagad, hei, tas ir brīdis, kad es parasti lūdzu tevi nopirkt manu grāmatu, bet šodien tā vietā lūgsim tevi nopirkt Migela grāmatu. Migels Eskobars un Kens Puls uzrakstīja šo izcilo grāmatu par M Is For (DATA) MONKEY - labāko grāmatu, kas pieejama vietnē Power Query. Ej, pārbaudi to.
Labi, sakrājies: šodien ir patiešām gara epizode; mums ir skatītājs, mēs lejupielādējam datus no sistēmas, kur katru vienumu atdala ar ALT + ENTER, un mēs cenšamies veikt VLOOKUP katram atsevišķam vienumam; šodien izveidoja risinājumu, izmantojot Power Query, ieskaitot strukturēto kolonnu rīku ekstrakts kā; bet tas darbojas tikai sarakstā, nevis tabulā, tāpēc man bija jāizmanto funkcija TABLE.COLUMN, lai tabulu pārveidotu par sarakstu.
Nu, hei. Es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2151.xlsm
