Mai multe lucruri ciudate de la autorul www.excelguru.ca ...

Recent am primit un comentariu la una dintre postările mele de pe blog care îmi cerea cum să separ valorile și textul, mai ales atunci când nu există un delimitator comun, cum ar fi un spațiu care să le separe. Acesta este un pic cam interesant, deoarece nu există o funcție evidentă pentru ca acest lucru să se întâmple.

valorile

Scenariul pe care îl are utilizatorul aici este o listă de valori cu unitatea lor de măsură, similară cu aceasta:

Această problemă este că nu avem cu adevărat nimic care să împartă cu ușurință acest lucru, deoarece nu există un model bun. Uneori există spații după valori, alteori nu. Literele se schimbă și nu există consecvență în ceea ce privește câte caractere reprezintă valorile. Deci, cum ați aborda acest lucru?

Cred că o soluție pentru acest tip de problemă va fi specifică datelor utilizate. Privind datele eșantionului, m-am gândit că probabil pot să mă bazez pe toate numerele aflate la începutul șirului și că probabil nu voi vedea ceva de genul metri pătrați exprimat ca m2. Desigur, dacă această presupunere nu a fost corectă, ar trebui să vin cu o altă metodă.

În orice caz, unghiul pe care l-am abordat a fost acela de a construi o funcție personalizată pentru a elimina valorile numerice principale. Asta ar trebui să mă lase cu valorile textului, pe care apoi le-aș putea înlocui în șirul original. Hai să aruncăm o privire.

Așa cum recomandăm în M este pentru Data Monkey, modalitatea de a construi o funcție personalizată este de a începe cu o interogare obișnuită care ne va permite să parcurgem fiecare piesă pe care trebuie să o faceți.

Așadar, concentrându-mă pe acest lucru prin interfața cu utilizatorul, iată cum am început această soluție.

  • Creați o nouă interogare de alimentare -> din alte surse -> interogare necompletată
  • În bara de formule, am tastat 1,07 kg (fără ghilimele, doar textul respectiv) și am apăsat Enter
  • Apoi am făcut clic dreapta pe text în fereastra Power Query și am ales să îl convertesc într-o listă

Desigur, nu puteți face o grămadă cu Liste în interfața cu utilizatorul, așa că am convertit-o într-un tabel:

  • Instrumente de listă -> Transformare -> În tabel -> OK

Pentru a fi corect, aș fi putut începe prin crearea unei înregistrări sau a unei liste de la zero (așa cum vă arătăm cum să faceți în M este pentru Data Monkey), dar nu am avut nevoie de asta aici pentru a mă ridica și alergând repede. Indiferent, acum stau într-un loc frumos, unde am expusă întreaga interfață pentru a face ceea ce am nevoie (care a fost obiectivul meu inițial).

În acest moment, lucrurile devin destul de ușoare:

  • Faceți clic dreapta pe Coloana1 -> Înlocuiți valorile -> Înlocuiți 0 cu nimic
  • Repetați pentru 1 până la 9 și caracterul zecimal

Acest lucru a eliminat toate numerele și zecimalele, lăsându-mă cu doar text. Dar, pentru că știu că unele dintre valori au și spații în ele, ar trebui să mă ocup de asta:

  • Faceți clic dreapta pe Coloana1 -> Transformare -> Decupare

Ultimul lucru pe care l-am făcut a fost să descopăr punctul de date acolo, întrucât nu vreau să returnez un tabel atunci când îl convertesc într-o funcție. Pentru a face acest lucru, am avut nevoie să:

  • Faceți clic pe fx din stânga barei de formule
  • Adăugați următoarele la textul din bara de formule: [Coloana1]

Observați că acum avem doar punctul de date, nu antetul Column1.

Acum, avem o funcție mică, care îmi va permite să iau un punct de date, să-l igienizez și să-l transform într-un punct de date fără valori principale. Dar cum pot să-l reutilizez pentru al folosi pentru fiecare înregistrare? Răspunsul este să transformați această interogare într-o funcție personalizată, așa cum descriem în capitolul 22 din M este pentru Data Monkey. Iată cum o facem:

  • Accesați Vizualizare -> Editor avansat
  • Chiar înainte de linia „let”, adăugați următoarele:

  • Mergeți și plasați două/caractere în fața liniei sursei curente pentru a o comenta (altfel ar suprascrie funcția de intrare)
  • Faceți clic pe Terminat
  • Redenumiți interogarea în fxRemoveNumbers

Asta este. L-am convertit într-o funcție. Acum puteți accesa Acasă -> Închidere și încărcare pentru ao salva și este gata de utilizare. Partea interesantă aici este că crearea logicii este partea dificilă, convertirea acesteia într-o funcție este ușor de moarte.

Așadar, acum să folosim noua noastră funcție pentru a separa valorile și textul. Iată cum am făcut acest lucru:

  • Selectați orice celulă din tabel -> creați o nouă interogare -> Din tabel
  • Accesați Adăugați coloană -> Adăugați coloană personalizată
    • Nume coloană nouă: Măsurați
    • Formula coloanei: fxRemoveNumbers ([Cantitate])

Și avem o nouă coloană frumoasă, cu doar valorile textuale.

Nu este rău, acum trebuie doar să găsim o modalitate de a înlocui textul care se potrivește în coloana Cantitate cu nimic ... După ce am verificat ghidul de formulare MSDN Power Query, am găsit o formulă numită Text.Replace () care pare că ar trebui să facă exact asta:

  • Accesați Adăugați coloană -> Adăugați coloană personalizată
    • Nume coloană nouă: valoare
    • Formula coloanei: = Text.Replace ([Cantitate], [Măsura], ")

Pentru a rezuma aici, vom analiza ceea ce este în coloana Cantitate și vom înlocui orice instanță a textului din coloana Măsurare cu valoarea dintre cele două seturi de ghilimele (adică nimic.) Rezultatele sunt prezentate mai jos:

Acum este doar o chestiune simplă de a face o curățare:

  • Faceți clic dreapta pe coloana Valoare -> Modificare tip -> Număr zecimal
  • Faceți clic dreapta pe coloana Cantitate -> Eliminați

Și iată-te. S-a terminat. Pur și simplu trebuie să mergem la Acasă -> Închidere & Încărcare pentru a-l comite, și apoi să îl reîmprospătăm oricând avem nevoie.

Cartea este acum disponibilă și este plină de informații bune care vă vor ajuta să rezolvați această problemă, precum și multe altele. Aflați mai multe despre carte aici.