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

În postarea din această săptămână vom reveni la postarea originală despre modul de separare a valorilor și a textului în interogarea de alimentare din 4 noiembrie 2015. Această postare a atras câteva sugestii de la Imke Feldman și Bill Szysz cu metode mai bune.

Alte postări pe această temă

Toate cele trei postări sunt strâns legate și, ca reamintire, de care aveți nevoie depinde de situația dvs. specifică, desigur. Iată cele trei iterații:

  • Postarea mea originală pentru a împărți măsurătorile lăsând doar numerele (acest lucru va funcționa numai dacă nu există numere în măsurare.)
  • Continuarea numerelor (sau a textului) folosind abordarea lui Imke Feldman
  • Această postare, folosind metoda lui Bill Szysz pentru a împărți măsurătorile

Deci, de ce trebuie să examinăm acest lucru din nou? Ei bine, realitatea este că soluția pe care am construit-o a funcționat perfect pentru setul de date pe care l-am folosit. Cu toate acestea, Bill a redat câteva date diferite care arătau astfel:

separate

Acum, prietenul meu Scott vă va spune că utilizatorul (voi parafraza acest lucru), „ar trebui să primească o lecție severă despre practicile adecvate de introducere a datelor”, dar dacă datele sunt deja în sistem ... este prea târziu și trebuie să ne ocupăm Cu acesta.

Dacă ați încercat metoda mea, veți descoperi că nu reușește, după cum se arată mai jos:

Practic, orice măsură care conține un număr sau virgule sau spații la mijloc ... toate sunt ucigașe pentru rutina mea. Așa că Bill mi-a trimis un e-mail pentru a-mi arăta cum va aborda situația.

Am rupt puțin pașii înapoi de la trimiterea inițială a lui Bill și l-am construit într-un mod în care cred că majoritatea utilizatorilor vor aborda acest lucru așa cum veți vedea. (Prezentarea inițială a lui Bill a fost un pic mai perfectă, dar arăt cum aș fi ajuns acolo încercând să construiesc cu ce a ajuns el.)

Pasul 1: Trageți datele

Desigur, pentru început, avem nevoie de date ...

  • Creați o nouă interogare -> Din tabel
  • Faceți clic dreapta pe coloana Cantitate -> Transformare -> minuscule

Acest ultim pas este de fapt destul de important. Motivul este că acum vrem să împărțim datele în prima instanță a unui caracter între A și z. Deoarece Power Query este sensibil la majuscule și minuscule, forțarea textului la minusculă înseamnă că nu vom pierde împărțirea pe baza unui caracter din A la Z a stabilit. De asemenea, înseamnă că oferim Power Query mai puțin de procesat, deoarece trebuie să caute doar 26 de caractere, nu 52 (atât cu litere mici, cât și cu litere mari).

Pasul 2: separați valorile și textul

Acum, că știm ce vrem să facem, să o facem. Să împărțim textul cu primul caracter alfa:

  • Accesați Adăugați o coloană -> Adăugați o coloană personalizată
    • Nume coloană nouă -> Valoare
    • Formula personalizată a coloanei:

Această formulă este destul de interesantă, deoarece se va împărți cu unul dintre caracterele dintre ghilimele. Deoarece am forțat textul să scadă cu minuscule, acesta va reacționa la orice literă a alfabetului de la a-z sau A-Z. Dar există o mică problemă care returnează o listă, nu textul:

Întrucât suntem interesați doar de primul articol din această listă în acest moment (tot ceea ce precede prima literă), putem modifica formula pentru a trece doar la primul element. Pentru a face asta:

  • Faceți clic pe pictograma roată din dreptul pasului Adăugat personalizat din fereastra Pași aplicați
  • Modificați formula pentru a citi după cum urmează:

Amintindu-ne că Power Query contează de la o bază de zero și că numărul dintre acoladele ne permite să pătrundem într-un anumit element din listă, vom primi apoi o listă care include doar primul element, după cum urmează:

Cu aceasta realizată, putem extrage valorile rămase din dreapta folosind unele funcții de text. (Puteți afla mai multe despre acestea în postarea mea pe 5 Formule de text foarte utile - ediția Power Query sau citind capitolul 17 din M este pentru Data Monkey)

  • Accesați Adăugați o coloană -> Adăugați o coloană personalizată
    • Nume coloană nouă -> Măsurați
    • Formula personalizată a coloanei:

În acest moment, putem identifica o problemă în modul în care am parcurs procesul. Poți să-l vezi?

În setul de date original, L (pentru litri) a fost valorificat. În rezultatul nostru, nu este. Dacă nu vă pasă de acest lucru, treceți peste pasul 3, dar dacă credeți că acest lucru este important ... trebuie să ne modificăm puțin pașii.

Pasul 3: Remediați pașii minusculi

Am cauzat problema afișată mai sus convertind coloana Cantitate cu litere mici. Deoarece acea coloană rămâne în jur, avem nevoie de ea pentru a păstra formatul original, astfel încât să putem împărți măsura și să păstrăm cazul corect pentru caractere. Dar, în mod ideal, am dori să facem acest lucru fără modificând acea formulă originală după cum urmează:

Așa cum? Dacă avem nevoie de el convertit pentru a ne împărți cu o listă mai mică, ce trebuie să facem?

Răspunsul este să cuibărim pasul cu litere mici în primul nostru pas personalizat adăugat. Să modificăm primul pas personalizat adăugat:

  • Faceți clic pe pictograma roată din dreptul pasului Adăugat personalizat
  • Modificați formula pentru a citi după cum urmează:

= Text.SplitAny (Text.Lower [Cantitate]), "abcdefghijklmnopqrstuvwxyz")

Acum, să eliminăm pasul Text cu litere mici și să vedem dacă funcționează în continuare (nu uitați să selectați pasul Personalizat adăugat1 după ce ați eliminat pasul Text cu litere mici:

Notă: Dacă vă așteptați la acest lucru de la început, este evident că nu este nevoie să convertiți în minuscule, cauzați eroarea și remediați-o. Ați putea sări peste durere și doar să înfășurați coloana într-o funcție Text.Lower () pentru a începe. Motivul pentru care am arătat această rută este că consider că iterez frecvent în acest mod atunci când îmi construiesc propriile scenarii de curățare a datelor.

Pasul 4: Curățarea finală

Singurul lucru rămas de făcut este să convertiți coloana Valoare în numere. Veți primi o eroare dacă o încercați, deoarece există încă spații în mijlocul unor numere (virgulele sunt în regulă, dar spațiile nu sunt.)

  • Faceți clic dreapta pe coloana Valoare -> Înlocuiți valorile
    • Valoare de găsit -> un singur spațiu
    • Înlocuiți cu -> lăsați această zonă necompletată
  • Faceți clic dreapta pe coloana Valoare -> Modificare tip -> Număr zecimal

  • Redenumiți interogarea
  • Accesați Acasă -> Închidere și încărcare