Înțelegerea și crearea tabelelor de date în Power Pivot în Excel

Tabelele de date din Power Pivot sunt esențiale pentru parcurgerea și calcularea datelor în timp. Acest articol oferă o înțelegere aprofundată a tabelelor de date și a modului în care le puteți crea în Power Pivot. Mai exact, acest articol descrie:

• De ce este important un tabel de date pentru parcurgerea și calcularea datelor după date și ore.
• Cum se utilizează Power Pivot pentru a adăuga un tabel de date la modelul de date.
• Cum se creează coloane de date noi, cum ar fi An, Lună și Perioadă într-un tabel de date.
• Cum să creați relații între tabelele de date și tabelele de informații.
• Cum să lucrezi cu timpul.

Acest articol este destinat utilizatorilor începători în Power Pivot. Totuși, este important să aveți deja o bună înțelegere a importului de date, a relațiilor și a creării coloanelor și măsurilor calculate.

Acest articol nu descrie cum să utilizați funcțiile DAX Time-Intelligence în formulele de măsură. Pentru mai multe informații despre cum să creați măsuri cu funcțiile DAX Time Intelligence, consultați Time Intelligence în Power Pivot din Excel.

Cuprins

• Înțelegerea tabelelor de date

• Adăugarea unui tabel de date la modelul de date

  • Importul dintr-o bază de date relațională

  • Crearea unui tabel de date în Excel

    • Cum să: Creați un tabel de date în Excel și copiați-l în modelul de date

• Adăugarea coloanelor de date noi în tabelul de date

  • Funcțiile DAX pentru dată și oră

  • Exemple de formule pentru un an calendaristic

    • An
    • Lună
    • Trimestru
    • Numele lunii

  • Exemple de formule pentru un an fiscal

    • An fiscal
    • Lună fiscală
    • Trimestru fiscal

• Sărbători sau date speciale

• Calendar particularizat - treisprezece perioade de patru săptămâni

  • Săptămână
  • Punct
  • Perioadă An fiscal
  • Perioada în anul fiscal

• Relații

  • Mai multe relații
  • Relații inactive
  • Tabele de date multiple

• Proprietatea Tabel dată

• Lucrul cu timpul

• Faceți datele mai ușor de utilizat

• Anexă

  • Conversia datelor de tip de date text într-un tip de date dată
  • Resurse suplimentare

Înțelegerea tabelelor de date

Aproape toate analizele de date implică navigarea și compararea datelor în date și ore. De exemplu, poate doriți să adunați totalurile de vânzări pentru ultimul trimestru fiscal și să comparați acele totaluri cu alte trimestre sau poate doriți să calculați un sold de închidere la sfârșitul lunii pentru un cont. În fiecare dintre aceste cazuri, utilizați datele ca o modalitate de a grupa și a agrega tranzacțiile sau soldurile de vânzări pentru o anumită perioadă de timp.

Raport Power View

Un tabel de date poate conține numeroase reprezentări diferite ale datelor și orei. De exemplu, un tabel de date calendaristice va avea adesea coloane precum An fiscal, Lună, Trimestru sau Perioadă, pe care le puteți selecta drept câmpuri dintr-o listă de câmpuri atunci când împărțiți și filtrați datele în rapoartele PivotTable sau Power View.

Listă de câmpuri Power View

Pentru ca coloanele de date, cum ar fi An, Luna și Trimestrul, să includă toate datele din intervalul respectiv, tabelul de date trebuie să aibă cel puțin o coloană cu un set contiguu de date. Aceasta înseamnă că acea coloană trebuie să aibă un rând pentru fiecare zi din fiecare an inclus în tabelul de date.

De exemplu, dacă datele pe care doriți să le parcurgeți au date cuprinse între 1 februarie 2010 și 30 noiembrie 2012 și raportați pentru un an calendaristic, atunci veți dori un tabel de date cu cel puțin un interval de date cuprinse între 1 ianuarie 2010 și 31 decembrie 2012. Fiecare an din tabelul de date trebuie să conțină toate zilele pentru fiecare an. Dacă veți reîmprospăta periodic datele cu date mai noi, se recomandă să derulați data de sfârșit cu un an sau doi, astfel încât să nu fie necesar să actualizați tabelul de date pe măsură ce trece timpul.

Tabel de date cu un set contiguu de date

Dacă raportați pentru un an fiscal, puteți crea un tabel de date cu un set contiguu de date pentru fiecare an fiscal. De exemplu, dacă anul fiscal începe la 1 martie și aveți date pentru anii fiscali 2010 până la data curentă (de exemplu, în anul fiscal 2013), puteți crea un tabel de date care începe la 1.03.2009 și include cel puțin fiecare zi din fiecare an fiscal, până la ultima dată din anul fiscal 2013.

Dacă veți raporta atât pentru anul calendaristic, cât și pentru anul fiscal, nu trebuie să creați tabele de date separate. Un singur tabel de date calendaristice poate include coloane pentru un an calendaristic, un an fiscal și chiar un calendar de treisprezece patru săptămâni. Important este că tabelul de date conține un set contiguu de date pentru toți anii incluși.

Adăugarea unui tabel de date la modelul de date

Există mai multe modalități în care puteți adăuga un tabel de date la modelul de date:

• Importul dintr-o bază de date relațională sau din altă sursă de date.
• Creați un tabel de date în Excel, apoi copiați sau creați o legătură la un tabel nou în Power Pivot.
• Importați din Microsoft Azure Marketplace.

Să le analizăm mai atent pe fiecare dintre acestea.

Importul dintr-o bază de date relațională

Dacă importați unele date sau toate datele dintr-un depozit de date sau alt tip de bază de date relațională, există șanse să existe deja un tabel de date și relații între acesta și restul de date pe care le importați. Datele și formatul se vor potrivi probabil cu datele din datele despre fapte, iar datele încep probabil mult în trecut și se extind departe în viitor. Tabelul de date pe care doriți să-l importați poate fi foarte mare și poate conține un interval de date dincolo de cel care va trebui inclus în modelul de date. Puteți utiliza caracteristicile avansate de filtrare a Expertului import tabel din Power Pivot pentru a alege selectiv doar datele și coloanele specifice de care aveți cu adevărat nevoie. Acest lucru poate reduce semnificativ dimensiunea registrului de lucru și poate îmbunătăți performanța.

Table Import Wizard

În majoritatea cazurilor, nu va trebui să creați coloane suplimentare, cum ar fi An fiscal, Săptămână, Nume lună etc., deoarece acestea vor exista deja în tabelul importat. Totuși, în unele cazuri, după ce ați importat tabelul de date în modelul de date, poate fi necesar să creați coloane de date suplimentare, în funcție de o anumită necesitate de raportare. Din fericire, acest lucru este ușor de făcut folosind DAX. Veți afla mai multe despre crearea câmpurilor de tabel de date mai târziu. Fiecare mediu este diferit. Dacă nu sunteți sigur dacă sursele de date au o dată sau un tabel de calendar asociat, solicitați administratorului bazei de date.

Crearea unui tabel de date în Excel

Puteți să creați un tabel de date în Excel și apoi să îl copiați într-un tabel nou din modelul de date. Acest lucru este într-adevăr destul de ușor de făcut și vă oferă multă flexibilitate.

Când creați un tabel de date în Excel, începeți cu o singură coloană cu un interval contiguu de date. Apoi puteți crea coloane suplimentare, cum ar fi An, Trimestru, Lună, An fiscal, Perioadă etc. în foaia de lucru Excel utilizând formule Excel sau, după ce copiați tabelul în modelul de date, le puteți crea ca coloane calculate. Crearea de coloane de date suplimentare în Power Pivot este descrisă în secțiunea Adăugarea de coloane de date noi la Tabelul de date, mai jos în acest articol.

Cum să: Creați un tabel de date în Excel și copiați-l în modelul de date

1. În Excel, într-o foaie de lucru necompletată, în celula A1, tastați un nume de antet de coloană pentru a identifica un interval de date. De obicei, acesta va fi ceva de genul Dată, DatăOră sau CheieDată.

2. În celula A2, tastați o dată de început. De exemplu, 01.01.2010.

3. Faceți clic pe instrumentul de umplere și glisați-l în jos la un număr de rând care include o dată de sfârșit. De exemplu, 31.12.2016.
   

4. Selectați toate rândurile din coloana Dată (inclusiv numele antetului din celula A1).

5. În grupul Stiluri , faceți clic pe Formatare ca tabel, apoi selectați un stil.

6. În caseta de dialog Formatare ca tabel , faceți clic pe OK.
   

7. Copiați toate rândurile, inclusiv antetul.

8. În Power Pivot, pe fila Pornire , faceți clic pe Lipire.

9. În Examinare lipire Nume>tabel, tastați un nume, cum ar fi Dată sau Calendar. Lăsați bifată opțiunea Utilizare primul rând ca anteturi de coloană, apoi faceți clic pe OK.
   
   Noul tabel de date (denumit Calendar în acest exemplu) din Power Pivot arată astfel:
   

   Notă

   De asemenea, puteți crea un tabel legat, utilizând Adăugare la modelul de date. Totuși, acest lucru face ca registrul de lucru să fie imens în mod inutil, deoarece registrul de lucru are două versiuni ale tabelului de date; una în Excel și una în Power Pivot.

Acum aveți un tabel de date în modelul de date. Puteți adăuga coloane noi de date, cum ar fi An, Lună etc., utilizând DAX.

Adăugarea coloanelor de date noi în tabelul de date

Un tabel de date cu o singură coloană de date calendaristice care are un rând pentru fiecare zi din fiecare an este important pentru definirea tuturor datelor dintr-un interval de date. De asemenea, este necesar pentru crearea unei relații între tabelul de informații și tabelul de date. Dar acea coloană cu o singură dată cu un rând pentru fiecare zi nu este utilă atunci când analizați după date într-un raport PivotTable sau Power View. Se recomandă ca tabelul de date să includă coloane care vă ajută să agregați datele pentru un interval sau un grup de date. De exemplu, poate doriți să adunați volumul vânzărilor în funcție de lună sau trimestru sau să creați o măsură care calculează creșterea de la an la an. În fiecare dintre aceste cazuri, tabelul de date are nevoie de coloane de an, lună sau trimestru care vă permit să agregați datele pentru acea perioadă.

Dacă ați importat tabelul de date dintr-o sursă de date relațională, acesta poate include deja tipurile diferite de coloane de date dorite. În unele cazuri, poate veți dori să modificați unele dintre acele coloane sau să creați coloane de date suplimentare. Acest lucru este valabil mai ales dacă vă creați propriul tabel de date în Excel și îl copiați în modelul de date. Din fericire, crearea de coloane de date noi în Power Pivot este destul de simplă cu funcțiile pentru dată și oră din DAX.

Funcțiile DAX pentru dată și oră

Dacă ați lucrat vreodată cu funcții de dată și oră în formule Excel, probabil că veți fi familiarizat cu funcțiile de dată și oră. Deși aceste funcții sunt similare cu cele ale lor din Excel, există câteva diferențe importante:

• Funcțiile DAX Date și Time utilizează un tip de date dată/oră.
• Ei pot prelua valori dintr-o coloană ca argument.
• Acestea pot fi utilizate pentru a returna și/sau a manipula valori dată.

Aceste funcții sunt utilizate adesea atunci când creați coloane de date particularizate într-un tabel de date, deci este important să le înțelegeți. Vom utiliza o serie de astfel de funcții pentru a crea coloane pentru An, Trimestru, LunăFiscală și așa mai departe.

DAX include următoarele funcții pentru dată și oră:

• DATA
• DATEVALUE
• ZIUA URMĂTOARE
• EDATE
• EOMONTH
• HOUR
• MINUTE
• MONTH
• NOW
• SECOND
• TIME
• TIMEVALUE
• ASTĂZI
• WEEKDAY
• WEEKNUM
• YEAR
• YEARFRAC

Există multe alte funcții DAX pe care le puteți utiliza și în formulele dvs. De exemplu, multe dintre formulele descrise aici utilizează funcții matematice și trigonometrice ca MOD și TRUNC, funcții logice ca IF și funcții text precum FORMAT Pentru mai multe informații despre alte funcții DAX, consultați secțiunea Resurse suplimentare de mai jos în acest articol.

Exemple de formule pentru un an calendaristic

Următoarele exemple descriu formulele utilizate pentru a crea coloane suplimentare într-un tabel de date denumit Calendar. O coloană, denumită Dată, există deja și conține un interval contiguu de date cuprinse între 01.01.2010 și 31.12.2016.

An

=YEAR([data])

În această formulă, funcția YEAR returnează anul începând cu valoarea din coloana Dată. Deoarece valoarea din coloana Dată este de tip de date datăoră, funcția YEAR știe cum să returneze anul din aceasta.

Lună

=MONTH([data])

În această formulă, la fel ca în cazul funcției YEAR, putem utiliza funcția MONTH pentru a returna o valoare de lună din coloana Date.

Trimestru

=INT(([Lună]+2)/3)

În această formulă, utilizăm funcția INT pentru a returna o valoare dată ca un întreg. Argumentul pe care îl specificăm pentru funcția INT este valoarea din coloana Lună, adunăm 2 și apoi împărțim la 3 pentru a obține trimestrul nostru, de la 1 la 4.

Numele lunii

=FORMAT([data],"mmmm")

În această formulă, pentru a obține numele lunii, utilizăm funcția FORMAT pentru a efectua conversia unei valori numerice din coloana Dată în text. Specificăm coloana Dată ca primul argument, apoi formatul; Dorim ca numele lunii noastre să afișeze toate caracterele, așa că utilizăm "mmmm". Rezultatul nostru arată astfel:

Dacă dorim să returnăm numele lunii abreviat la trei litere, vom utiliza "mmm" în argumentul format.

Ziua săptămânii

=FORMAT([data],"ddd")

În această formulă, utilizăm funcția FORMAT pentru a obține numele zilei. Pentru că dorim doar un nume de zi abreviat, specificăm "ddd" în argumentul format.

Raport PivotTable eșantion

Odată ce aveți câmpuri pentru date, cum ar fi An, Trimestru, Lună etc., le puteți utiliza într-un raport PivotTable sau într-un raport. De exemplu, următoarea imagine arată câmpul CantitateVânzări din tabelul de informații despre vânzări din VALORI și An și Trimestru din tabelul de dimensiuni calendar din RÂNDURI. VolumVânzări este agregată pentru contextul anului și trimestrului.

Exemple de formule pentru un an fiscal

An fiscal

=IF([Lună]<= 6,[An],[An]+1)

În acest exemplu, anul fiscal începe la 1 iulie.

Nu există nicio funcție care să poată extrage un an fiscal dintr-o valoare de dată, deoarece datele de început și de sfârșit pentru un an fiscal sunt adesea diferite de cele ale unui an calendaristic. Pentru a obține anul fiscal, utilizăm mai întâi o funcție IF pentru a testa dacă valoarea pentru Lună este mai mică sau egală cu 6. În al doilea argument, dacă valoarea pentru Month este mai mică sau egală cu 6, atunci returnați valoarea din coloana An. Dacă nu, returnați valoarea din Year și adăugați 1.

Altă modalitate de a specifica o valoare de lună de sfârșit de an fiscal este să creați o măsură care specifică pur și simplu luna. De exemplu, FYE:=6. Apoi puteți face referire la numele măsurii în locul numărului lunii. De exemplu, =IF([Lună]<=[Calcul],[An],[An]+1). Acest lucru oferă mai multă flexibilitate atunci când faceți referire la luna de sfârșit de an fiscal în mai multe formule diferite.

Lună fiscală

=IF([Lună]<= 6; 6+[Lună], [Lună]- 6)

În această formulă, specificăm dacă valoarea pentru [Lună] este mai mică sau egală cu 6, atunci luăm 6 și adunăm valoarea din Lună, altfel scădem 6 din valoarea din [Lună].

Trimestru fiscal

=INT(([LunăFiscală]+2)/3)

Formula pe care o utilizăm pentru Trimestrul fiscal este aproape aceeași ca pentru Trimestru în anul calendaristic. Singura diferență este că specificăm [LunăFiscală] în loc de [Lună].

Sărbători sau date speciale

Se recomandă să includeți o coloană de date care indică faptul că anumite date sunt sărbători sau altă dată specială. De exemplu, poate doriți să adunați totalurile de vânzări pentru ziua de Anul Nou adăugând un câmp Sărbători într-un raport PivotTable, ca slicer sau filtru. În alte cazuri, poate doriți să excludeți acele date din alte coloane de date sau într-o măsură.

Includerea sărbătorilor sau a zilelor speciale este destul de simplă. Puteți crea un tabel în Excel care are datele pe care doriți să le includeți. Apoi puteți copia sau utiliza Adăugare la modelul de date pentru a-l adăuga la modelul de date ca tabel legat. În majoritatea cazurilor, nu este necesar să creați o relație între tabel și tabelul Calendar. Toate formulele care fac referire la aceasta pot utiliza funcția LOOKUPVALUE pentru a returna valori.

Mai jos este un exemplu de tabel creat în Excel care include sărbători de adăugat la tabelul de date:

Data	Sărbătoare
1/1/2010	Anul Nou
11/25/2010	Ziua Recunoștinței
12/25/2010	Crăciun
01.01.11	Anul Nou
11/24/2011	Ziua Recunoștinței
12/25/2011	Crăciun
01.01.12	Anul Nou
11/22/2012	Ziua Recunoștinței
12/25/2012	Crăciun
1/1/2013	Anul Nou
11/28/2013	Ziua Recunoștinței
12/25/2013	Crăciun
11/27/2014	Ziua Recunoștinței
12/25/2014	Crăciun
01.01.2014	Anul Nou
11/27/2014	Ziua Recunoștinței
12/25/2014	Crăciun
1/1/2015	Anul Nou
11/26/2014	Ziua Recunoștinței
12/25/2015	Crăciun
01.01.16	Anul Nou
11/24/2016	Ziua Recunoștinței
12/25/2016	Crăciun

În tabelul de date, creăm o coloană numită Sărbători și utilizăm o formulă ca aceasta:

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[Dată])

Să analizăm mai atent această formulă.

Utilizăm funcția LOOKUPVALUE pentru a obține valorile din coloana Sărbători din tabelul Sărbători. În primul argument, specificăm coloana în care va fi valoarea rezultatului. Specificăm coloana Sărbători în tabelul Sărbători , deoarece aceasta este valoarea care dorim să fie returnată.

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[Dată])

Apoi specificăm al doilea argument, coloana de căutare care conține datele pe care dorim să le căutăm. Specificăm coloana Dată în tabelul Sărbători , astfel:

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[Dată])

În sfârșit, specificăm coloana din tabelul Calendar care conține datele pe care dorim să le căutăm în tabelul Sărbători . Aceasta este, desigur, coloana Dată din tabelul Calendar .

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[Dată])

Coloana Sărbători va returna numele sărbătorii pentru fiecare rând care are o valoare de dată ce se potrivește cu o dată din tabelul Sărbători.

Calendar particularizat - treisprezece perioade de patru săptămâni

Unele organizații, cum ar fi comerțul cu amănuntul sau serviciile de alimentație alimentară, raportează adesea perioade diferite, cum ar fi treisprezece perioade de patru săptămâni. Cu un calendar de treisprezece patru săptămâni, fiecare perioadă este de 28 de zile; Prin urmare, fiecare perioadă conține patru zile de luni, patru marți, patru zile de miercuri și așa mai departe. Fiecare perioadă conține același număr de zile și, de obicei, sărbătorile se vor încadra în aceeași perioadă în fiecare an. Puteți alege să începeți o menstruație în orice zi din săptămână. La fel ca în cazul datelor dintr-un calendar sau dintr-un an fiscal, puteți utiliza DAX pentru a crea coloane suplimentare cu date particularizate.

În exemplele de mai jos, prima perioadă completă începe în prima duminică a anului fiscal. În acest caz, anul fiscal începe la 01 septembrie.

Săptămână

Această valoare ne dă numărul săptămânii începând cu prima săptămână completă a anului fiscal. În acest exemplu, prima săptămână completă începe duminică, astfel că prima săptămână completă din primul an fiscal din tabelul Calendar începe de fapt pe 4.07.2010 și continuă pe parcursul ultimei săptămâni întregi din tabelul Calendar. Deși această valoare în sine nu este atât de utilă în analiză, este necesar să o calculați pentru a fi utilizată în alte formule pentru o perioadă de 28 de zile.

=INT([data]-40356)/7)

Să analizăm mai atent această formulă.

Mai întâi, creăm o formulă care returnează valorile din coloana Dată ca număr întreg, astfel:

=INT([data])

Apoi vrem să căutăm prima duminică din primul an fiscal. Vedem că este 04.07.2010.

Acum, scădeți 40356 (care este întregul pentru 27.06.2010, ultima duminică din anul fiscal anterior) din acea valoare pentru a obține numărul de zile de la începutul zilelor din tabelul nostru Calendar, astfel:

=INT([data]-40356)

Apoi împărțiți rezultatul la 7 (zile dintr-o săptămână), astfel:

=INT(([data]-40356)/7)

Rezultatul arată astfel:

Punct

Perioada din acest calendar particularizat conține 28 de zile și va începe întotdeauna într-o duminică. Această coloană va returna numărul perioadei începând cu prima duminică din primul an fiscal.

=INT(([Săptămână]+3)/4)

Să analizăm mai atent această formulă.

Mai întâi, creăm o formulă care returnează o valoare din coloana Săptămână ca număr întreg, astfel:

= INT([Săptămână])

Apoi adăugați 3 la acea valoare, astfel:

=INT([Săptămână]+3)

Apoi împărțiți rezultatul la 4, astfel:

=INT(([Săptămână]+3)/4)

Rezultatul arată astfel:

Perioadă An fiscal

Această valoare returnează anul fiscal pentru o perioadă.

=INT(([Punct]+12)/13)+2008

Să analizăm mai atent această formulă.

Mai întâi, creăm o formulă care returnează o valoare din Punct și adună 12:

=([Punct]+12)

Împărțim rezultatul la 13, deoarece există treisprezece perioade de 28 de zile în anul fiscal:

=(([Punct]+12)/13)

Adăugăm 2010, deoarece acesta este primul an din tabel:

=(([Punct]+12)/13)+2010

În cele din urmă, utilizăm funcția INT pentru a elimina orice fracție a rezultatului și returnăm un număr întreg, când este împărțit la 13, astfel:

= INT(([Punct]+12)/13)+2010

Rezultatul arată astfel:

Perioada în anul fiscal

Această valoare returnează numărul perioadei, de la 1 la 13, începând cu prima perioadă completă (începând cu duminică) din fiecare an fiscal.

=IF(MOD([Punct],13), MOD([Punct],13),13)

Această formulă este puțin mai complexă, așa că o vom descrie mai întâi într-o limbă pe care o înțelegem mai bine. Această formulă afirmă că împărțiți valoarea din [Perioadă] la 13 pentru a obține un număr de perioadă (1-13) din an. Dacă acel număr este 0, returnează 13.

Mai întâi, creăm o formulă care returnează restul valorii din Punct cu 13. Putem folosi MOD (funcțiile matematice și trigonometrice) astfel:

= MOD([Punct],13)

Acest lucru, în cea mai mare parte, ne oferă rezultatul dorit, cu excepția cazului în care valoarea pentru Perioadă este 0, deoarece acele date nu se încadrează în primul an fiscal, la fel ca în primele cinci zile din tabelul nostru de date calendaristice exemplu. Ne putem ocupa de aceasta cu o funcție IF. În cazul în care rezultatul nostru este 0, returnăm 13, astfel:

= IF(MOD([Perioadă],13),MOD([Perioadă],13),13)

Rezultatul arată astfel:

Raport PivotTable eșantion

Imaginea de mai jos afișează un raport PivotTable cu câmpul CantitateVânzări din tabelul Informații despre vânzări în VALORI și câmpurile PeriodFiscalYear și PeriodInFiscalYear din tabelul de dimensiune de date calendaristice din RÂNDURI. SalesAmount este agregată pentru context după anul fiscal și perioada de 28 de zile din anul fiscal.

Relații

După ce ați creat un tabel de date în modelul de date, pentru a începe să navigați prin datele din rapoartele PivotTable și din rapoarte și pentru a agrega date pe baza coloanelor din tabelul de dimensiuni de date, trebuie să creați o relație între tabelul de fapte cu datele tranzacției și tabelul de date.

Pentru că trebuie să creați o relație bazată pe date, se recomandă să vă asigurați că creați acea relație între coloane ale căror valori sunt de tipul de date dată/oră (Dată).

Pentru fiecare valoare de date din tabelul de informații, coloana de căutare asociată din tabelul de date trebuie să conțină valori care se potrivesc. De exemplu, un rând (înregistrare tranzacție) din tabelul Date vânzări cu o valoare 15.08.2012 12:00 AM din coloana CheieDată trebuie să aibă o valoare corespunzătoare în coloana Dată asociată din tabelul de date (denumit Calendar). Acesta este unul dintre cele mai importante motive pentru care doriți ca coloana de date din tabelul de date să conțină un interval contiguu de date care să includă orice dată posibilă în tabelul de informații.

Dacă primiți o eroare de nepotrivire tip atunci când încercați să creați o relație, motivul este probabil faptul că coloana din tabelul de informații nu este de tip de date Dată. Acest lucru se poate întâmpla atunci când Power Pivot nu poate efectua automat conversia unui tip de date non-dată (de obicei un tip de date text) într-un tip de date dată. Puteți utiliza în continuare coloana în tabelul de informații, dar va trebui să efectuați conversia datelor cu o formulă DAX într-o coloană calculată nouă. Consultați Conversia datelor din tipul de date text într-un tip de date dată , mai jos în anexă.

Mai multe relații

În unele cazuri, poate fi necesar să creați mai multe relații sau să creați mai multe tabele de date. De exemplu, dacă există mai multe câmpuri de date în tabelul de informații despre vânzări, cum ar fi DateKey, ShipDate și ReturnDate, toate pot avea relații cu câmpul Date din tabelul de date Calendar, dar numai unul dintre acestea poate fi o relație activă. În acest caz, deoarece DateKey reprezintă data tranzacției și, prin urmare, cea mai importantă dată, aceasta ar servi cel mai bine drept relație activă . Ceilalți au relații inactive.

Următorul raport PivotTable calculează vânzările totale după an fiscal și trimestrul fiscal. O măsură denumită Total vânzări, cu formula Total vânzări:=SUM([CantitateVânzări]), este plasată în VALORI, iar câmpurile AnFiscal și TrimestruFiscal din tabelul de date calendaristice sunt plasate în RÂNDURI.

Acest raport PivotTable simplu funcționează corect, deoarece dorim să adunăm vânzările noastre totale după data tranzacției din DateKey. Măsura noastră Total vânzări utilizează datele din DateKey și este însumată după anul fiscal și trimestrul fiscal, deoarece există o relație între DateKey (CheieDată) din tabelul Vânzări și coloana Dată din tabelul de date calendaristice.

Relații inactive

Dar ce se întâmplă dacă am dori să însumăm vânzările noastre totale nu după data tranzacției, ci după data livrării? Avem nevoie de o relație între coloana DatăExpediere din tabelul Vânzări și coloana Dată din tabelul Calendar. Dacă nu creăm această relație, agregările noastre se bazează întotdeauna pe data tranzacției. Cu toate acestea, putem avea mai multe relații, chiar dacă numai una poate fi activă, și deoarece data tranzacției este cea mai importantă, obține relația activă cu tabelul Calendar.

În acest caz, DatăExpediere are o relație inactivă, astfel încât orice formulă de măsură creată pentru a agrega date pe baza datelor de expediere trebuie să specifice relația inactivă utilizând funcția USERELATIONSHIP .

De exemplu, deoarece există o relație inactivă între coloana DatăExpediere din tabelul Vânzări și coloana Dată din tabelul Calendar, putem crea o măsură care însumează totalul vânzărilor după data livrării. Utilizăm o formulă ca aceasta pentru a specifica relația de utilizat:

Total vânzări după data livrării:=CALCULATE(SUM(Vânzări[VolumVânzări]), USERELATIONSHIP(Vânzări[DatăLivrare], Calendar[Dată]))

Această formulă afirmă simplu: Calculați o sumă pentru VolumVânzări, dar filtrați utilizând relația dintre coloana DatăExpediere din tabelul Vânzări și coloana Dată din tabelul Calendar.

Acum, dacă creăm un raport PivotTable și punem măsura Total vânzări după data livrării în VALORI și An fiscal și Trimestru fiscal în RÂNDURI, vedem același Total general, dar toate celelalte sume pentru anul fiscal și trimestrul fiscal sunt diferite, deoarece se bazează pe data expedierii, nu pe data tranzacției.

Utilizarea relațiilor inactive vă permite să utilizați un singur tabel de date, dar necesită ca toate măsurile (cum ar fi Totalul vânzărilor după data livrării) să facă referire la relația inactivă în formula sa. Există o altă alternativă, adică utilizați mai multe tabele de date.

Tabele de date multiple

Altă modalitate de a lucra cu mai multe coloane de date calendaristice în tabelul de informații este să creați mai multe tabele de date și să creați relații active separate între ele. Să ne uităm din nou la exemplul tabelului Vânzări. Avem trei coloane cu date după care am putea dori să agregăm date:

• O cheie de dată cu data vânzării pentru fiecare tranzacție.
• A ShipDate - cu data și ora la care au fost expediate clientul articolele vândute.
• A ReturnDate - cu data și ora la care s-a primit unul sau mai multe elemente returnate.

Rețineți că câmpul CheieDată cu data tranzacției este cel mai important. Vom face majoritatea agregărilor pe baza acestor date, așa că cu siguranță vom dori o relație între aceasta și coloana Dată din tabelul Calendar. Dacă nu dorim să creăm relații inactive între DatăExpediere și ReturnDate și câmpul Dată din tabelul Calendar, necesitând astfel formule de măsuri speciale, putem crea tabele de date suplimentare pentru data expedierii și data returnării. Apoi putem crea relații active între ele.

În acest exemplu, am creat alt tabel de date denumit CalendarExpediere. Desigur, acest lucru înseamnă și crearea de coloane de date suplimentare și, deoarece aceste coloane de date se află într-un alt tabel de date, dorim să le denumim astfel încât să le diferențieze de aceleași coloane din tabelul Calendar. De exemplu, am creat coloane denumite YearShip, ShipMonth, ShipQuarter și așa mai departe.

Dacă creăm raportul PivotTable și punem măsura Total vânzări în VALORI și ShipFiscalYear și ShipFiscalQuarter în ROWS, vedem aceleași rezultate pe care le-am văzut atunci când am creat o relație inactivă și un câmp calculat special Total vânzări după data livrării.

Fiecare dintre aceste abordări necesită o analiză atentă. Atunci când utilizați mai multe relații cu un singur tabel de date, poate fi necesar să creați măsuri speciale care tranzitează relațiile inactive, utilizând funcția USERELATIONSHIP. Pe de altă parte, crearea mai multor tabele de date poate fi derutantă într-o listă de câmpuri, iar pentru că aveți mai multe tabele în modelul de date, va fi nevoie de mai multă memorie. Experimentați cu ceea ce funcționează cel mai bine pentru dvs.

Proprietatea Tabel dată

Proprietatea Tabel de date setează metadatele necesare pentru ca funcțiile Time-Intelligence, cum ar fi TOTALYTD, PREVIOUSMONTH și DATESBETWEEN, să funcționeze corect. Atunci când un calcul este rulat utilizând una dintre aceste funcții, motorul de formule Power Pivot știe unde să meargă pentru a obține datele de care are nevoie.

Când setați proprietatea Tabel date, specificați un tabel de date și o coloană de date cu tipul de date Dată (datăoră) în acesta.

Cum să: Setați proprietatea Tabel de date

1. În fereastra PowerPivot, selectați tabelul Calendar .
2. Pe fila Proiectare , faceți clic pe Marcare ca tabel de date.
3. În caseta de dialog Marcare ca tabel de date, selectați o coloană cu valori unice și tipul de date Dată.

Lucrul cu timpul

Toate valorile dată cu un tip de date Dată din Excel sau SQL Server sunt de fapt un număr. În acest număr sunt incluse cifre care se referă la oră. În multe cazuri, fiecare rând este miezul nopții. De exemplu, dacă un câmp CheieDatăOră dintr-un tabel cu informații despre vânzări are valori precum 19.10.2010 12:00:00 AM, acest lucru înseamnă că valorile sunt la nivelul de precizie al zilei. Dacă valorile câmpului CheieDatăOră au o oră inclusă, de exemplu, 19.10.2010 8:44:00 AM, acest lucru înseamnă că valorile sunt la nivelul de precizie minut. Valorile pot fi, de asemenea, egale cu precizia la nivel de oră sau chiar la nivelul secundelor. Nivelul de precizie al valorii temporale va avea un impact semnificativ asupra modului în care creați tabelul de date și relațiile dintre acesta și tabelul de informații.

Trebuie să determinați dacă veți agrega datele la un nivel de precizie zilnic sau la un nivel de precizie temporal. Cu alte cuvinte, poate doriți să utilizați coloanele din tabelul de date, cum ar fi Dimineața, După-amiaza sau Ora, ca câmpuri de dată și oră în zonele Rând, Coloană sau Filtru ale unui raport PivotTable.

Dacă intenționați să agregați datele la nivelul de oră, atunci tabelul de date va avea nevoie de o coloană de date cu ora inclusă. De fapt, va avea nevoie de o coloană de date cu un rând pentru fiecare oră, sau poate chiar la fiecare minut, al fiecărei zile, pentru fiecare an din intervalul de date. Acest lucru se datorează faptului că, pentru a crea o relație între coloana CheieDatăOră din tabelul de informații și coloana de date din tabelul de date, trebuie să aveți valori care să se potrivească. După cum vă puteți imagina, dacă includeți mulți ani, tabelul de date poate fi foarte mare.

Însă, în majoritatea cazurilor, doriți să agregați datele doar zilei. Cu alte cuvinte, veți utiliza coloane precum An, Lună, Săptămână sau Zi a săptămânii ca câmpuri în zonele Rând, Coloană sau Filtrare dintr-un raport PivotTable. În acest caz, coloana de date din tabelul de date trebuie să conțină numai un rând pentru fiecare zi dintr-un an, așa cum am descris anterior.

În cazul în care coloana de date include un nivel de precizie de timp, dar veți agrega doar la un nivel de zi, pentru a crea relația dintre tabelul de date și tabelul de date, poate fi necesar să modificați tabelul de date creând o coloană nouă care trunchiază valorile din coloana de date la o valoare de zi. Cu alte cuvinte, conversia unei valori precum 19.10.2010 8:44:00 AM la 19.10.2010 12:00:00 AM. Apoi puteți crea relația dintre această coloană nouă și coloana de date din tabelul de date, deoarece valorile se potrivesc.

Să analizăm un exemplu. Această imagine afișează o coloană CheieDatăOră în tabelul de informații despre vânzări. Toate agregările pentru datele din acest tabel trebuie să fie doar la nivelul zilei, utilizând coloane din tabelul de date calendaristice, cum ar fi An, Lună, Trimestru etc. Ora inclusă în valoare nu este relevantă, ci doar data propriu-zisă.

Pentru că nu trebuie să analizăm aceste date la nivelul de oră, nu avem nevoie ca coloana Dată din tabelul de date din Calendar să includă un rând pentru fiecare oră și fiecare minut al fiecărei zile din fiecare an. Deci, coloana Dată din tabelul de date arată astfel:

Pentru a crea o relație între coloana CheieDatăOră din tabelul Vânzări și coloana Dată din tabelul Calendar, putem să creăm o nouă coloană calculată în tabelul Informații despre vânzări și să utilizăm funcția TRUNC pentru a trunchia valoarea de dată și oră din coloana CheieDatăOră într-o valoare de dată care se potrivește cu valorile din coloana Dată din tabelul Calendar. Formula noastră arată astfel:

=TRUNC([CheieDatăOră],0)

Acest lucru ne oferă o coloană nouă (numită DateKey) cu data din coloana DateTimeKey și ora 12:00:00 AM pentru fiecare rând:

Acum putem crea o relație între această coloană nouă (CheieDată) și coloana Dată din tabelul Calendar.

În mod similar, putem crea o coloană calculată în tabelul Vânzări care reduce precizia orei în coloana CheieDatăOră la nivelul de precizie al orei. În acest caz, funcția TRUNC nu va funcționa, dar putem utiliza în continuare alte funcții DAX Date and Time pentru a extrage și a re-concatena o valoare nouă la un nivel de precizie orar. Putem utiliza o formulă ca aceasta:

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

Noua noastră coloană arată astfel:

Dacă coloana noastră Dată din tabelul de date are valori la nivelul de precizie a orei, putem crea o relație între ele.

Faceți datele mai ușor de utilizat

Multe dintre coloanele de date pe care le creați în tabelul de date sunt necesare pentru alte câmpuri, dar nu sunt chiar utile în analiză. De exemplu, câmpul CheieDată din tabelul Vânzări la care am făcut referire și pe care l-am afișat în acest articol este important, deoarece, pentru fiecare tranzacție, tranzacția respectivă este înregistrată ca având loc la o anumită dată și oră. Dar, din punct de vedere al analizei și raportării, nu este chiar atât de util, deoarece nu îl putem utiliza ca rând, coloană sau câmp filtru într-un raport Pivot Table sau raport.

În mod similar, în exemplul nostru, coloana Dată din tabelul Calendar este foarte utilă, de fapt esențială, dar nu o puteți utiliza ca dimensiune într-un raport PivotTable.

Pentru a păstra tabelele și coloanele din ele cât mai utile și pentru a simplifica navigarea în listele de câmpuri din rapoartele PivotTable sau Power View, este important să ascundeți coloanele inutile din instrumentele client. De asemenea, poate doriți să ascundeți și anumite tabele. Tabelul Sărbători afișat mai devreme conține date de sărbători care sunt importante pentru anumite coloane din tabelul Calendar, dar nu puteți utiliza coloanele Dată și Sărbători din tabelul Sărbători ca câmpuri într-un raport PivotTable. Din nou, pentru a simplifica navigarea în listele de câmpuri, puteți să ascundeți întregul tabel Sărbători.

Un alt aspect important al lucrului cu datele îl reprezintă convențiile de denumire. Puteți denumi tabelele și coloanele din Power Pivot orice doriți. Dar rețineți că, mai ales dacă veți partaja registrul de lucru cu alți utilizatori, o convenție bună de denumire simplifică identificarea tabelelor și datelor, nu doar în listele de câmpuri, ci și în Power Pivot și în formulele DAX.

După ce aveți un tabel de date în Modelul de date, puteți începe să creați măsuri care vă vor ajuta să profitați la maximum de date. Unele pot fi simple, cum ar fi însumarea totalurilor de vânzări pentru anul curent, iar altele pot fi mai complexe, unde trebuie să filtrați după un anumit interval de date unice. Aflați mai multe în Măsuri în Power Pivot și funcțiile Time Intelligence.

Anexă

Conversia datelor de tip de date text într-un tip de date dată

În unele cazuri, un tabel de fapte cu date despre tranzacție poate conține date de tip date text. Mai exact, o dată care apare ca 2012-12-04T11:47:09 nu este de fapt o dată sau cel puțin nu este tipul de dată pe care Power Pivot îl poate înțelege. De fapt, este doar un text care se citește ca o dată. Pentru a crea o relație între o coloană de date calendaristice din tabelul de informații și o coloană de date dintr-un tabel de date, ambele coloane trebuie să fie de tipul de date Dată .

De obicei, atunci când încercați să modificați tipul de date pentru o coloană de date care sunt tip de date text într-un tip de date dată, Power Pivot poate interpreta datele și le poate converti automat într-un tip de date cu date reale. Dacă Power Pivot nu poate efectua o conversie a tipului de date, veți primi o eroare de nepotrivire tip.

Totuși, puteți efectua conversia datelor într-un tip de date dată reală. Puteți să creați o nouă coloană calculată și să utilizați o formulă DAX pentru a analiza anul, luna, ziua, ora etc. din șirurile de text, apoi să o alăturați într-un mod în care Power Pivot poate fi citit ca o dată calendaristică reală.

În acest exemplu, am importat un tabel de fapte denumit Vânzări în Power Pivot. Conține o coloană numită DateTime. Valorile apar astfel:

Dacă ne uităm la tipul de date din grupul Formatare Fila Pornire din Power Pivot, vedem că este tipul de date Text.

Nu putem crea o relație între coloana DatăOră și coloana Dată din tabelul de date, deoarece tipurile de date nu se potrivesc. Dacă încercăm să modificăm tipul de date la Dată, primim o eroare de nepotrivire de tip:

În acest caz, Power Pivot nu a reușit conversia tipului de date din text în date calendaristice. Putem utiliza în continuare această coloană, dar, pentru a o obține într-un tip de date cu dată reală, trebuie să creăm o coloană nouă care analizează textul și îl creează din nou într-o valoare Power Pivot poate crea un tip de date Dată.

Rețineți, din secțiunea Lucrul cu timpul de mai sus în acest articol; Dacă nu este necesar ca analiza să fie la un nivel de precizie al zilei, ar trebui să efectuați conversia datelor din tabelul de informații la un nivel de precizie al zilei. Cu acest lucru în minte, dorim ca valorile din noua coloană să fie la nivelul de precizie al zilei (excluzând ora). Putem efectua conversia valorilor din coloana DatăOră într-un tip de date dată și eliminăm nivelul de precizie al orei cu următoarea formulă:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Aceasta ne dă o coloană nouă (în acest caz, numită Dată). Power Pivot detectează chiar și valorile ca fiind date și setează tipul de date automat la Dată.

Dacă dorim să păstrăm nivelul de precizie al timpului, extindem pur și simplu formula pentru a include orele, minutele și secundele.

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Acum, că avem o coloană Dată cu tipul de date Dată, putem crea o relație între aceasta și o coloană de date dintr-o dată.

Resurse suplimentare

Datele calendaristice în PowerPivot

Calculele în Power Pivot

Introducere rapidă: Aflați noțiunile de bază despre DAX în 30 de minute

Referință pentru expresiile de analiză a datelor

Centrul de resurse DAX