Perpetual Holiday Calendar – POTW #12 (Power Query Solution)

I det här blogginlägget ska vi utforska min lösning på . För den här utmaningen besökte vi LuckyTemplates första Veckans problem-utmaning, där vi skapade en evig semesterkalender från en blandning av specifika och relativa datum. Men den här gången kommer vi bara att använda Power Query . Du kan se hela videon av denna handledning längst ner på den här bloggen.

Om du har fastnat i själva problemuppdelningen kan du gå tillbaka till den för att se om det finns element i någon av tillvägagångssätten som kan hjälpa dig med den här utmaningen.

När jag läste bidragen har jag sett en blandning av människor som utnyttjar användargränssnittet och skriver anpassade funktioner för att skapa en evig kalender. Det har varit roligt att analysera varje bidrag, och om du vill bli inspirerad uppmuntrar jag dig att göra detsamma.

När jag själv arbetade igenom det här scenariot skapade jag flera lösningar. Den jag gick med illustrerar bäst hur du inte bara kan bryta ner ett problem i mindre bitar utan också göra dem synliga . Detta är mycket användbart om du stöter på ett fel eller oväntade resultat.

Innehållsförteckning

• Utgångspunkten för att skapa en evig kalender
• Hämtar åren från datumtabellen
• Få siffran från dagkolumnen
• Åberopar den anpassade funktionen för den eviga semesterkalendern
• Skapa en lista över datum
• Tillämpa villkorlig logik
• Granska M-koden för den eviga semesterkalendern
• Slutsats

Utgångspunkten för att skapa en evig kalender

Detta råa semesterbord är vår utgångspunkt. För att sätta ihop ett datum behöver vi ett år, en månad och en dag för specifika datum. Allt som saknas är året.

Men för relativa datum måste vi göra lite mer arbete. Till att börja med måste vi dela upp den dagkolumnen i både siffror och veckodag. Låt oss granska dessa steg.

Hämtar åren från datumtabellen

För att göra den här lösningen dynamisk kommer jag att hämta årtal från datumtabellen . I formelfältet kan du se att jag har använt en projektion för att få en enda kolumntabell från min datumtabell. Detta kommer naturligtvis att innehålla dubbletter. För att ta bort dessa har jag lagt till Table.Distinct .

När jag klickar åt sidan i det vita utrymmet bredvid någon av dessa tabeller, kommer du också att se en lista med de distinkta årtal som finns i min datumtabellsfråga.

Vi fick ett månadsnamn , men vi behöver faktiskt ett månadsnummer . Nu, när vi sammanfogar ett år till ett månadsnamn, kan vi använda Date.From- funktionen för att returnera den första i den månaden som ett datumvärde.

Genom att lägga till Date.Month , har vi bara månadsnumret för det specifika datumet. För januari returnerar den en 1. För februari returnerar den en 2, och så vidare.

Få siffran från dagkolumnen

För att få siffran från dagkolumnen bestämde vi oss för att inte dela upp kolumnen utan extrahera värdet istället. Som du kan se i formelfältet använde vi Text.BeforeDelimiter och använde det utrymmet för att identifiera den högra och vänstra delen av textsträngen.

Om det inte finns något utrymme vill vi returnera det som finns kvar i dagkolumnen. Så för den första posten returnerar det en 1. För den andra posten som innehåller det utrymmet returnerar det den första delen av textsträngen, ordet Third .

Vi kommer att använda ett liknande tillvägagångssätt för att få datumnamnet, men använder funktionen List.PositionOf . Detta returnerar den nollbaserade offseten för värdet i listan som skickats som första argument, och det returnerar -1 om värdet inte visas i den listan.

För den första posten passerar den värdet 1, som inte finns i vår lista. Det är därför den returnerar värdet -1. För den andra posten returnerar den värdet Monday .

Den skickar det till vår funktion och returnerar en 1 eftersom det är det andra objektet i vår lista. Därefter ersätter vi alla -1-värden med en null .

Vid det här laget har vi skapat alla byggstenar vi behöver för att anropa den anpassade funktionen.

Åberopar den anpassade funktionen för den eviga semesterkalendern

I formelfältet kan vi se att funktionen tar fyra argument: ett år , en månad , en siffra och en veckodag .

Men hur bestämmer det datumvärdet? Vi kommer att använda List.PositionOf igen för att få ett numeriskt värde för siffran. Vi har en lista som innehåller första, andra, tredje och fjärde. När vi skickar siffran till List.PositionOf returnerar den den nollbaserade offseten.

Återigen, om ett värde inte finns, returnerar det en -1. Så för den första posten skickar vi värdet 1. 1 finns inte i den här listan, vilket är anledningen till att det returnerar värdet -1. För den andra posten passerar vi värdet Third, som är den tredje posten i vår lista. Detta returnerar värdet 2.

Skapa en lista över datum

Låt oss nu skapa en lista med datum. Vi bestämmer startdatumet för den månaden, bestämmer antalet dagar i den månaden och använder List.Dates för att skapa en lista som innehåller alla dagar i den månaden.

Därefter kommer vi att använda List.Select för att bara behålla de datum som är på en specifik veckodag.

När vi klickar till sidan bredvid någon av dessa listor kan vi se att den bara innehåller dessa fyra objekt.

Tillämpa villkorlig logik

Slutligen kommer vi att tillämpa villkorlig logik för att konstruera eller hämta ett datum. Om veckodagen är null , vilket indikerar att vi har ett specifikt datum, kan vi använda det inneboende #date för att konstruera ett datumvärde.

Om siffran är lika med sist, kan vi använda List.Reverse -funktionen för att vända ordningen i listan och extrahera det första värdet.

Om vi ​​vill ändra denna stigande ordning till fallande, kan vi alltid extrahera det sista datumet från det datumintervallet. Det spelar ingen roll om vi har fyra eller fem element i listan. Genom att vända beställningen och extrahera den första varan har vi alltid sista datum.

För alla andra fall använder vi List.Skip .

Vi kan extrahera värdet baserat på denna position på listan. För Thanksgiving day är siffran fjärde, och fjärde returnerar en 3. Detta kommer att hoppa över de tre första värdena i listan, och kommer alltid att returnera det fjärde objektet.

Låt oss slå ihop allt och gå till lösningsfrågan. För källsteget anropar vi datumtabellen för RAW helgdagar. Därefter lägger vi till en kolumn som returnerar en lista med datum.

Granska M-koden för den eviga semesterkalendern

Låt oss kontrollera M-koden för kolumnen. För varje rad i tabellen kapslade vi en tabell som innehåller de distinkta åren från datumtabellen. Sedan transformerade vi värdena i den årskolumnen genom att anropa den anpassade funktionen.

För att kunna anropa den anpassade funktionen måste vi naturligtvis göra några inställningar först. Vi måste få månadsnumret , det numeriska värdet och naturligtvis veckodagen . Sedan behöll vi listan med datum från den kapslade tabellen.

När vi klickar åt sidan i det vita utrymmet kan vi se en förhandsvisning av listan nedan.

Nästa steg är att utöka listan och lägga till CelebratedOnDate .

Detta är logiken vi använde i den här beräkningen: om datumet är på en lördag, så kommer vi att subtrahera en dag så att vi landar på en fredag. Om det är en söndag, kommer vi att lägga till en dag för att landa på en måndag. Om det inte är en lördag eller söndag, så vill vi ha en noll.

Slutligen är det sista steget att ändra typerna.

Slutsats

Bara en snabb påminnelse om detär en pågående serie som händer var 1:a och 3:e onsdag på LuckyTemplates-forumet. Problemet som publicerades den 1:a veckan kräver en DAX-baserad lösning, medan den 3:e veckans problem kräver en Power Query-baserad.

Om du gillade innehållet i denna POTW, vänligen prenumerera på LuckyTemplates TV-kanal för mer. Vi har en enorm mängd innehåll som kommer ut hela tiden från mig själv och en rad innehållsskapare, alla dedikerade till att förbättra ditt sätt att använda LuckyTemplates och Power Platform.

Melissa