Adăugarea mai multor foi în Excel la LuckyTemplates

Acest tutorial va discuta despre cum să importați și să deschideți un fișier Excel cu mai multe foi într-un singur tabel LuckyTemplates.

Acest fișier Excel de date va fi folosit pentru acest tutorial.

Fișierul conține 60 de foi de date cu același aspect.

Obiectivul acestui tutorial este de a încărca toate foile Excel în LuckyTemplates și de a prelua informațiile Bond ca un singur tabel. Trebuie să obțineți simbolul de securitate și să îl adăugați într-o coloană separată. Apoi, adăugați-le pe toate într-un singur tabel care folosește interfața de utilizare pentru a genera codul de care aveți nevoie.

Cuprins

• Importul foilor Excel în LuckyTemplates
• Aducerea tickerului de securitate de la Excel la LuckyTemplates
• Eliminarea rândurilor goale
• Duplicarea unei interogări
• Atribuirea tipurilor de date coloanelor
• Adăugarea coloanei Ticker de securitate din Excel la LuckyTemplates
• Crearea unei coloane personalizate pentru interogarea obligațiunilor
• Concluzie

Importul foilor Excel în LuckyTemplates

Primul lucru de făcut este să deschideți Editorul Power Query și să creați un parametru pentru a stoca locația fișierului folosind opțiunea Gestionare parametri .

În caseta Manage Parameters, faceți clic pe New și introduceți FileLocation ca nume parametru. Apoi, setați Tipul de date la Text și Valori sugerate la Orice valoare . Copiați calea fișierului Excel și inserați-o în câmpurile Valoare curentă.

După ce apăsați OK, faceți clic pe Nouă sursă și selectați Excel Workbook .

Apoi, selectați și deschideți fișierul Excel. Odată terminat, ecranul Navigator se va deschide și va lista toate foile din fișierul Excel.

Selectați prima foaie și apăsați OK. După aceea, veți vedea tabelul în Editorul Power Query.

Următorul lucru de făcut este să schimbați calea fișierului codificată în parametrul fișierului. Deschideți fereastra Editor avansat și modificați calea fișierului din Codul sursă în FileLocation .

Cel mai bine este să aveți datele în tabelele Excel, deoarece limitele sau intervalul de date sunt definite înainte de a le aduce în Power Query . Deoarece fișierul conține foi și nu există tabele, riscă să aducă coloane și rânduri goale, așa că trebuie să le rezolvați.

Aducerea tickerului de securitate de la Excel la LuckyTemplates

Știind că aspectul foilor este fix, ajută la construirea unei soluții, mai ales dacă doriți să maximizați și să utilizați interfața cu utilizatorul pentru a genera codul. De exemplu, o cerință este să adăugați o coloană care conține simbolul de securitate. Dacă te uiți la date, poți vedea tickerul.

Interfața cu utilizatorul vă poate ajuta să extrageți valoarea tickerului. Faceți clic dreapta pe celulă și selectați Adăugare ca interogare nouă .

În bara de formule, veți vedea că numele tabelului este urmat de un număr de rând bazat pe zero între paranteze. Parantezele sunt numite operatori de index de poziție . Veți vedea, de asemenea, numele câmpului cuprins între paranteze pătrate, care se numesc operatori de acces la câmp .

Cu aceste coduri, acum puteți extrage valoarea. Reveniți la tabelul Bond și obțineți datele Bond. Mai întâi, scoateți primele 8 opt rânduri. Faceți clic pe Eliminați rândurile și selectați Eliminați rândurile de sus .

Apoi, introduceți 8 în câmpul Number Of Rows și apăsați OK.

Apoi, faceți clic pe Utilizați primul rând ca antete pentru a seta anteturile.

Odată terminat, tabelul Bond va arăta acum astfel.

Direct sub antet, veți vedea o linie subțire care reprezintă calitatea coloanei. De acolo, puteți vedea că există un număr destul de mare de spații libere în coloane. Aceasta înseamnă că fișierul a adus o mulțime de rânduri goale.

Eliminarea rândurilor goale

Pentru a elimina rândurile goale, faceți clic pe Eliminați rândurile și selectați Eliminați rândurile necompletate .

Această transformare generează această sintaxă:

Record.FieldValues ​​primește toate valorile din rândul curent din tabel ca o listă. List.RemoveMatchingItems elimină toate valorile din prima listă care are o potrivire în a doua listă. A doua listă conține doar un șir de text gol sau un nul. Acestea sunt valorile care vor fi excluse din prima listă.

Dacă toate șirurile de text goale și null au fost eliminate din lista cu valorile câmpului de înregistrare, lista ar trebui să fie goală și List.IsEmpty va fi evaluată la True. Apoi, Table.SelectRows va reține Adevărații.

Nu ar trebui să ajungeți la un tabel cu doar rânduri goale. De aceea cuvântul cheie not este adăugat înainte de List.IsEmpty . Apoi returnează un tabel care conține rânduri care nu sunt goale.

În afară de rândurile goale, trebuie să eliminați și coloanele goale. Dar înainte de asta, aruncați o privire la ce generează Power Query atunci când eliminați o coloană. Selectați a patra coloană și faceți clic pe Eliminați coloanele .

După efectuarea transformării, veți vedea această sintaxă în bara de formule.

Apelează funcția Table.RemoveColumns , apoi face referire și trece pasul anterior din panoul Pași aplicați ca prim argument. Transformarea transmite în continuare o listă care conține numele coloanelor pe care doriți să le eliminați.

Duplicarea unei interogări

Acum, duplicați interogarea și selectați pasul Promovare anteturi din panoul Pași aplicați. Apoi, faceți clic dreapta pe acel pas și selectați Ștergeți până la sfârșit .

Amintiți-vă că puteți utiliza operatorul de index de poziție pentru a trece un număr de rând bazat pe zero într-un set de paranteze. Așadar, păstrați doar primul rând introducând 0 în interiorul a două paranteze în bara de formule.

Odată terminat, tabelul Bond va arăta astfel.

De acolo, reutilizați o parte a logicii care a fost creată de pasul Rânduri necompletate eliminate pentru a transforma înregistrarea într-o listă și pentru a elimina valoarea nulă. Reveniți la interogarea Bond și selectați pasul Eliminați rândurile necompletate. Apoi, copiați acest cod M.

Reveniți la interogarea duplicată și inserați codul în bara de formule. Apoi, aranjați câteva șiruri pentru a face formula să arate așa.

Ați creat acum o listă cu nume de coloane pe care doriți să le păstrați. Redenumiți interogarea Nume coloane .

Apoi, reveniți la interogarea Bond. Deoarece ați creat o interogare care conține toate coloanele pe care doriți să le păstrați, trebuie să enumerați toate coloanele pe care doriți să le excludeți în funcția Table.RemoveColumns .

Schimbați {Column4} cu aceeași sintaxă care a fost copiată din pașii Eliminați rânduri goale. Schimbați și {“”, null} în ColumnNames .

Apoi, trebuie să treceți o listă cu numele coloanelor reale din tabelul Bond. Schimbați Record.FieldValues(_) în Table.ColumnNames() . Introduceți #”Removed Blank Rows” în interiorul parantezei pentru a trece un argument de referință la tabel.

Atribuirea tipurilor de date coloanelor

Următorul lucru de făcut este să atribuiți coloanelor tipuri de date adecvate. Pentru coloana Data, faceți clic pe pictograma de lângă antet și selectați Data.

Pentru coloanele PX_LAST și YLD_YTM_MID, selectați tipul de date Număr zecimal.

Cu aceste trei interogări, ați creat toate elementele de bază pe care le puteți utiliza pentru a proiecta o soluție care abordează toate foile din fișierul Excel . Pentru aceasta, trebuie să duplicați interogarea Bond și să ștergeți toți pașii, cu excepția pasului Sursă din panoul Pași aplicați.

Adăugarea coloanei Ticker de securitate din Excel la LuckyTemplates

În pasul Sursă, puteți vedea toate datele din fișierul Excel. În loc să creați o interogare separată pentru fiecare foaie, puteți utiliza interogarea Bond și puteți transforma tabelele imbricate în coloana Date.

Mai întâi, adăugați simbolul de securitate. Dacă faceți clic pe spațiul alb din interiorul celulei oricărui tabel, veți vedea o previzualizare a conținutului acelui tabel.

Trebuie să creați o logică folosind o instrucțiune pentru a obține tickerul de securitate. Accesați interogarea Coloana 2 și copiați adresa tickerului de securitate din bara de formule.

Apoi, reveniți la interogarea Bond și adăugați o coloană personalizată.

Denumiți coloana Security Ticker și scrieți următorul cod M.

Formula are o instrucțiune IF care spune că, dacă cuvântul Securitate este găsit în Coloana 1, va furniza valoarea celulei din Coloana 2 care este adiacentă acesteia. În caz contrar, se va acorda un nul.

După ce apăsați OK, o nouă coloană cu ticker-urile de securitate va fi adăugată la tabel.

Faceți clic pe butonul drop-down al coloanei Security Ticker și deselectați null pentru a elimina toate valorile nule din coloană.

După aceea, veți rămâne cu toate informațiile despre Obligațiuni din toate foile. Tot ce trebuie să faceți este să repetați transformările efectuate în interogarea Bond(2) și să le aplicați la tabelele imbricate din coloana Date.

Crearea unei coloane personalizate pentru interogarea obligațiunilor

Accesați interogarea Bond(2), deschideți fereastra Editor avansat și copiați următorul cod:

Apoi, reveniți la interogarea Bond și creați o altă coloană personalizată. Deoarece trebuie să aplicați mai multe transformări în mai mulți pași, trebuie să utilizați o instrucțiune let . Deci, introduceți lăsați și lipiți codul din Editorul avansat.

Apoi, schimbați Bond_Sheet la Date pentru a transforma tabelul în coloana Date.

După apăsarea OK, o nouă coloană va fi adăugată în tabel.

Dacă faceți clic pe spațiul alb din interiorul oricărei celule a acelei coloane, veți vedea datele din interogarea Bond(2).

Tot ce aveți nevoie este în ultimele două coloane ale interogării Bond. Deci, selectați atât coloanele Ticker de securitate, cât și Personalizate și faceți clic pe Eliminați alte coloane din panglica Eliminare coloane.

Extindeți coloana Personalizat și debifați caseta Utilizați numele original ca prefix.

Odată terminat, validați tabelul. Sub tabel, selectați opțiunea Profilare coloane pe baza întregului set de date .

După această transformare, va apărea o eroare în coloana Date.

Așadar, examinați eroarea făcând clic pe coloana Date și selectând Păstrați erorile în panglica Păstrați rândurile.

Dacă faceți clic pe valoarea Eroare din coloana Date, puteți citi mesajul de eroare de mai jos:

Pentru a rezolva problemele, mai întâi eliminați pasul Erori păstrate. Faceți clic pe coloana Date și selectați Eliminare erori din panglica Eliminare rânduri.

Apoi, setați opțiunea de profilare a coloanei înapoi la primele 1000 de rânduri. Si asta e!

Concluzie

Această soluție bazată pe interfața utilizator vă poate ajuta să atașați mai multe foi dintr-un fișier Excel la LuckyTemplates. În loc să creeze 60 de interogări separate și să efectueze toate transformările în mod repetat, această soluție poate crea o singură interogare care efectuează toate transformările. Utilizați și maximizați această soluție pentru a crea un raport de date grozav .

Melissa