Calendar perpetuu de vacanță – POTW #12 (Soluție Power Query)

În această postare pe blog, vom explora soluția mea la . Pentru această provocare, am reluat provocarea primară Problema săptămânii a lui LuckyTemplates, în care am creat un calendar de vacanță perpetuu dintr-un amestec de date specifice și relative. Dar de data aceasta, vom folosi doar Power Query . Puteți viziona videoclipul complet al acestui tutorial în partea de jos a acestui blog.

Dacă sunteți blocat cu defalcarea problemei în sine, puteți revizui prima pentru a vedea dacă există elemente în oricare dintre abordări care vă pot ajuta cu această provocare.

În timp ce citesc trimiterile, am văzut un amestec de oameni care foloseau interfața cu utilizatorul și scriau funcții personalizate pentru a crea un calendar perpetuu. A fost plăcut să analizezi fiecare intrare și, dacă vrei să te inspiri, te încurajez să faci același lucru.

Când lucram eu însumi la acest scenariu, am ajuns să creez mai multe soluții. Cea cu care am mers ilustrează cel mai bine modul în care nu numai că poți descompune o problemă în bucăți mai mici , ci și să le faci vizibile . Acest lucru este foarte util dacă întâmpinați o eroare sau rezultate neașteptate.

Cuprins

• Punctul de plecare pentru crearea unui calendar perpetuu
• Recuperarea Anilor din Tabelul Datelor
• Obținerea numărului din coloana Zilei
• Invocarea funcției personalizate pentru calendarul perpetuu de vacanță
• Crearea unei liste de date
• Aplicarea logicii condiționale
• Revizuirea Codului M pentru Calendarul Perpetuu al Sărbătorilor
• Concluzie

Punctul de plecare pentru crearea unui calendar perpetuu

Această masă brută de vacanță este punctul nostru de plecare. Pentru a reuni o dată, avem nevoie de un an, o lună și o zi pentru anumite date. Tot ce lipsește este anul.

Dar pentru datele relative, trebuie să lucrăm puțin mai mult. Pentru început, trebuie să împărțim acea coloană a zilei atât în ​​numeral, cât și în ziua săptămânii. Să revizuim acești pași.

Recuperarea Anilor din Tabelul Datelor

Pentru a face această soluție dinamică, voi prelua anii din tabelul de date . În bara de formule, puteți vedea că am folosit o proiecție pentru a obține un tabel cu o singură coloană din tabelul meu de date. Aceasta, desigur, va conține duplicate. Pentru a le elimina, am adăugat Table.Distinct .

Când dau clic în lateral în spațiul alb de lângă oricare dintre aceste tabele, veți vedea, de asemenea, o listă cu anii diferiți prezenți în interogarea mea pentru tabelul de date.

Ni s-a dat un nume de lună , dar de fapt vom avea nevoie de un număr de lună . Acum, când concatenăm un an cu un nume de lună, putem folosi funcția Date.From pentru a returna prima lună ca valoare de dată.

Adăugând Date.Month , ne rămâne doar numărul lunii pentru data respectivă. Pentru ianuarie, returnează un 1. Pentru februarie, returnează un 2 și așa mai departe.

Obținerea numărului din coloana Zilei

Pentru a obține cifra din coloana zilei, am decis să nu împărțim coloana și să extragem în schimb valoarea. După cum puteți vedea în bara de formule, am folosit Text.BeforeDelimiter și am folosit acel spațiu pentru a identifica partea din dreapta și din stânga acelui șir de text.

Dacă nu există spațiu, vrem să returnăm tot ce rămâne în coloana zilei. Deci, pentru prima înregistrare, aceasta returnează un 1. Pentru a doua înregistrare care conține acel spațiu, returnează prima parte a șirului de text, cuvântul Third .

Vom folosi o abordare similară pentru a obține numele datei, dar folosim funcția List.PositionOf . Aceasta returnează offset-ul pe bază de zero a valorii din lista transmisă ca prim argument și returnează -1 dacă valoarea nu apare în acea listă.

Pentru prima înregistrare, trece valoarea 1, care nu este prezentă în lista noastră. Acesta este motivul pentru care returnează valoarea -1. Pentru a doua înregistrare, returnează valoarea Monday .

Acesta transmite funcția noastră și returnează un 1 deoarece este al doilea element din lista noastră. În continuare, vom înlocui toate valorile -1 cu un null .

În acest moment, am creat toate blocurile de care avem nevoie pentru a invoca funcția personalizată.

Invocarea funcției personalizate pentru calendarul perpetuu de vacanță

În bara de formule, putem vedea că funcția ia patru argumente: un an , o lună , un număr și o zi a săptămânii .

Dar cum determină această valoare a datei? Vom folosi din nou List.PositionOf pentru a obține o valoare numerică pentru numeral. Avem o listă care conține primul, al doilea, al treilea și al patrulea. Când trecem numeralul în List.PositionOf , acesta returnează offset-ul bazat pe zero.

Din nou, dacă o valoare nu este prezentă, returnează un -1. Deci, pentru prima înregistrare, trecem valoarea 1. 1 nu este prezent în această listă, motiv pentru care returnează valoarea -1. Pentru a doua înregistrare, trecem valoarea Third, care este al treilea element din lista noastră. Aceasta returnează valoarea 2.

Crearea unei liste de date

Să creăm acum o listă de date. Vom determina data de începere pentru luna respectivă, vom determina numărul de zile din luna respectivă și vom folosi List.Dates pentru a crea o listă care să conțină toate zilele din luna respectivă.

În continuare, vom folosi List.Select pentru a păstra numai acele date care sunt într-o anumită zi a săptămânii.

Când facem clic în lateral lângă oricare dintre aceste liste, putem vedea că conține doar aceste patru elemente.

Aplicarea logicii condiționale

În cele din urmă, vom aplica logica condiționată pentru a construi sau a prelua o dată. Dacă ziua săptămânii este nulă , indicând că avem o anumită dată, putem folosi #date intrinsecă pentru a construi o valoare de dată.

Dacă numărul este egal cu ultimul, atunci putem folosi funcția List.Reverse pentru a inversa ordinea în listă și a extrage prima valoare.

Dacă dorim să schimbăm această ordine crescătoare în descrescătoare, putem oricând să extragem ultima dată din acel interval de date. Nu contează dacă avem patru sau cinci elemente în listă. Prin inversarea ordinii și extragerea primului articol, avem întotdeauna ultima dată.

Pentru toate celelalte cazuri, folosim List.Skip .

Putem extrage valoarea pe baza acestei poziții pe listă. Pentru Ziua Recunoștinței, cifra este al patrulea, iar al patrulea returnează un 3. Aceasta va omite primele trei valori din listă și va returna întotdeauna al patrulea element.

Să punem totul împreună și să mergem la interogarea soluției. Pentru pasul sursă, numim tabelul RAW pentru datele sărbătorilor. În continuare, vom adăuga o coloană care returnează o listă cu date.

Revizuirea Codului M pentru Calendarul Perpetuu al Sărbătorilor

Să verificăm codul M pentru coloană. Pentru fiecare rând din tabel, am imbricat un tabel care conține anii diferiți din tabelul de date. Apoi am transformat valorile din coloana respectivă a anului apelând funcția personalizată.

Desigur, pentru a putea invoca funcția personalizată, mai întâi trebuie să facem niște setări. Trebuie să obținem numărul lunii , valoarea numerică și, desigur, ziua săptămânii . Apoi, din acel tabel imbricat, am păstrat lista cu datele.

Când facem clic în lateral în spațiul alb, putem vedea o previzualizare a listei de mai jos.

Următorul pas este să extindeți acea listă și să adăugați CelebratedOnDate .

Aceasta este logica pe care am folosit-o în acest calcul: dacă data este într-o sâmbătă, atunci vom scădea o zi, astfel încât să ajungem într-o zi de vineri. Dacă este duminică, atunci vom adăuga o zi pentru a ateriza într-o zi de luni. Dacă nu este sâmbătă sau duminică, atunci vrem un nul.

În cele din urmă, ultimul pas este schimbarea tipurilor.

Concluzie

Doar o scurtă reamintire că este o serie în curs de desfășurare care are loc în fiecare prima și a treia miercuri pe forumul LuckyTemplates. Problema postată în săptămâna 1 necesită o soluție bazată pe DAX, în timp ce problema în săptămâna a 3-a cere una bazată pe Power Query.

Dacă v-a plăcut conținutul acoperit în acest POTW, vă rugăm să vă abonați la canalul TV LuckyTemplates pentru mai multe. Avem o cantitate imensă de conținut care iese tot timpul de la mine și de la o serie de creatori de conținut, toți dedicați îmbunătățirii modului în care utilizați LuckyTemplates și Power Platform.

Melissa