Evig feriekalender – POTW #12 (Power Query Solution)

I dette blogginnlegget skal vi utforske min løsning på . For denne utfordringen besøkte vi LuckyTemplates sin jomfru Ukens problem-utfordring, der vi laget en evig feriekalender fra en blanding av spesifikke og relative datoer. Men denne gangen vil vi bare bruke Power Query . Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Hvis du står fast på selve problemsammenbruddet, kan du gå tilbake til for å se om det er elementer i noen av tilnærmingene som kan hjelpe deg med denne utfordringen.

Mens jeg leste innsendingene, har jeg sett en blanding av mennesker som utnytter brukergrensesnittet og skriver tilpassede funksjoner for å lage en evigvarende kalender. Det har vært hyggelig å analysere hver oppføring, og hvis du ønsker å bli inspirert, oppfordrer jeg deg til å gjøre det samme.

Da jeg jobbet gjennom dette scenariet selv, endte jeg opp med å lage flere løsninger. Den jeg gikk med illustrerer best hvordan du ikke bare kan bryte ned et problem i mindre biter , men også gjøre dem synlige . Dette er veldig nyttig hvis du støter på en feil eller uventede resultater.

Innholdsfortegnelse

• Utgangspunktet for å lage en evigvarende kalender
• Henter årene fra datotabellen
• Få tallet fra Dag-kolonnen
• Påkaller den tilpassede funksjonen for den evigvarende feriekalenderen
• Opprette en liste over datoer
• Bruk av betinget logikk
• Gjennomgang av M-koden for den evigvarende feriekalenderen
• Konklusjon

Utgangspunktet for å lage en evigvarende kalender

Dette rå feriebordet er utgangspunktet vårt. For å sette sammen en dato trenger vi et år, en måned og en dag for bestemte datoer. Alt som mangler er året.

Men for relative datoer, må vi jobbe litt mer. For det første må vi dele den dagkolonnen inn i både tall og ukedag. La oss gå gjennom disse trinnene.

Henter årene fra datotabellen

For å gjøre denne løsningen dynamisk, skal jeg hente årene fra datotabellen . I formellinjen kan du se at jeg har brukt en projeksjon for å få en enkelt kolonnetabell fra datotabellen min. Dette vil selvfølgelig inneholde duplikater. For å fjerne disse har jeg lagt til Table.Distinct .

Når jeg klikker til siden i det hvite rommet ved siden av noen av disse tabellene, vil du også se en liste med de distinkte årene i datotabellspørringen min.

Vi fikk et månedsnavn , men vi trenger faktisk et månedsnummer . Nå, når vi setter sammen et år til et månedsnavn, kan vi bruke Date.From- funksjonen for å returnere den første i den måneden som en datoverdi.

Ved å legge til Date.Month , sitter vi igjen med bare månedsnummeret for den spesifikke datoen. For januar returnerer den en 1. For februar returnerer den en 2, og så videre.

Få tallet fra Dag-kolonnen

For å få tallet fra dagkolonnen, bestemte vi oss for å ikke dele opp kolonnen og trekke ut verdien i stedet. Som du kan se i formellinjen, brukte vi Text.BeforeDelimiter og brukte det mellomrommet til å identifisere høyre og venstre del av tekststrengen.

Hvis det ikke er plass, ønsker vi å returnere det som er igjen i dagkolonnen. Så for den første posten returnerer det en 1. For den andre posten som inneholder det mellomrommet, returnerer det den første delen av tekststrengen, ordet Third .

Vi vil bruke en lignende tilnærming for å få datonavnet, men bruker funksjonen List.PositionOf . Dette returnerer den nullbaserte forskyvningen av verdien i listen som ble sendt som første argument, og den returnerer -1 hvis verdien ikke vises i den listen.

For den første posten passerer den verdien 1, som ikke er til stede i listen vår. Dette er grunnen til at den returnerer verdien på -1. For den andre posten returnerer den verdien Monday .

Den overfører det til funksjonen vår og returnerer en 1 fordi det er det andre elementet i listen vår. Deretter erstatter vi alle -1-verdier med en null .

På dette tidspunktet har vi laget alle byggeklossene vi trenger for å starte den tilpassede funksjonen.

Påkaller den tilpassede funksjonen for den evigvarende feriekalenderen

I formellinjen kan vi se at funksjonen tar fire argumenter: et år , en måned , et tall og en ukedag .

Men hvordan bestemmer den datoverdien? Vi bruker List.PositionOf igjen for å få en numerisk verdi for tallet. Vi har en liste som inneholder første, andre, tredje og fjerde. Når vi sender tallet til List.PositionOf , returnerer det den nullbaserte offset.

Igjen, hvis en verdi ikke er til stede, returnerer den en -1. Så for den første posten sender vi verdien 1. 1 er ikke til stede i denne listen, og det er grunnen til at den returnerer verdien på -1. For den andre posten passerer vi verdien Third, som er det tredje elementet i listen vår. Dette returnerer verdien av 2.

Opprette en liste over datoer

La oss nå lage en liste over datoer. Vi bestemmer startdatoen for den måneden, bestemmer antall dager i den måneden og bruker List.Dates for å lage en liste som inneholder alle dagene i den måneden.

Deretter bruker vi List.Select for å bare beholde de datoene som er på en bestemt ukedag.

Når vi klikker til siden ved siden av noen av disse listene, kan vi se at den bare inneholder disse fire elementene.

Bruk av betinget logikk

Til slutt vil vi bruke betinget logikk for å konstruere eller hente en dato. Hvis ukedagen er null , noe som indikerer at vi har en bestemt dato, kan vi bruke den iboende #date for å konstruere en datoverdi.

Hvis tallet er lik sist, kan vi bruke List.Reverse- funksjonen for å snu rekkefølgen i listen og trekke ut den første verdien.

Hvis vi ønsker å endre denne stigende rekkefølgen til synkende, kan vi alltid trekke ut den siste datoen fra dette datointervallet. Det spiller ingen rolle om vi har fire eller fem elementer i listen. Ved å snu rekkefølgen og trekke ut den første varen har vi alltid siste dato.

For alle de andre tilfellene bruker vi List.Skip .

Vi kan trekke ut verdien basert på denne plasseringen på listen. For Thanksgiving day er tallet fjerde, og fjerde returnerer en 3. Dette vil hoppe over de tre første verdiene i listen, og vil alltid returnere det fjerde elementet.

La oss sette alt sammen og gå til løsningsspørsmålet. For kildetrinnet kaller vi RAW-feriedatotabellen. Deretter legger vi til en kolonne som returnerer en liste med datoer.

Gjennomgang av M-koden for den evigvarende feriekalenderen

La oss sjekke M-koden for kolonnen. For hver rad i tabellen nestet vi en tabell som inneholder de forskjellige årene fra datotabellen. Deretter transformerte vi verdiene i den årskolonnen ved å kalle den tilpassede funksjonen.

Selvfølgelig, for å kunne starte den tilpassede funksjonen, må vi gjøre litt oppsett først. Vi må få månedsnummeret , tallverdien og selvfølgelig ukedagen . Så fra den nestede tabellen holdt vi listen med datoer.

Når vi klikker til siden i det hvite rommet, kan vi se en forhåndsvisning av listen nedenfor.

Det neste trinnet er å utvide listen og legge til CelebratedOnDate .

Dette er logikken vi brukte i denne beregningen: hvis datoen er på en lørdag, så skal vi trekke fra en dag slik at vi lander på en fredag. Hvis det er en søndag, så legger vi til en dag for å lande på en mandag. Hvis det ikke er en lørdag eller søndag, vil vi ha en null.

Til slutt er det siste trinnet å endre typene.

Konklusjon

Bare en rask påminnelse om deter en pågående serie som skjer hver 1. og 3. onsdag på LuckyTemplates-forumet. Problemet som ble publisert den første uken krever en DAX-basert løsning, mens problemet i den tredje uken ber om en Power Query-basert.

Hvis du likte innholdet som dekkes i denne POTW, vennligst abonner på LuckyTemplates TV-kanal for mer. Vi har en enorm mengde innhold som kommer ut hele tiden fra meg selv og en rekke innholdsskapere, alle dedikert til å forbedre måten du bruker LuckyTemplates og Power Platform på.

Melissa