Van week naar maand

[MightyMike95]

Goedemiddag,

In Power Qeury heb ik twee kolommen: 1 weeknummer en 1 jaarnummer (bijv 20 en 2021. Ik wil hiervan de maand weten. Het moet dan staan als: 2021-04 bijvoorbeeld. Eigenlijk kom ik er denk ik wel uit als ik het weeknummer en jaarnummer naar een specifieke datum krijg (bijv eerste dag van de week)

In Excel ken ik de formule om naar een datum te gaan (daarna de maand formule gebruiken) als: MAX(DATUM($E$3;1;1);DATUM($E$3;1;1)-WEEKDAG(DATUM($E$3;1;1);2)+(J$5-1)*7+8)

Maar ik weet niet hoe ik die in Power Query kan krijgen. Kunnen jullie me helpen?

[g0tanks]

In principe kan je denk ik opzoeken hoe die functies in Power Query heten om zo de vertaling te maken. Voor WEEKDAG() kan je bijvoorbeeld gebruiken: https://docs.microsoft.com/nl-nl/powerquery-m/date-dayofweek

[Belindo]

Helaas gebruikt Power Query 'M' in plaats van de bekende Excel functies. Effectief is dat dus een tweede 'taal' die je moet beheersen.

Het beste is om stap voor stap te bereiken wat je wilt. Gebruik eerst wat hulpkolommen in Power Query, om aan het eind alles samen te voegen tot één formule.

Eerst het dikgedrukte stuk om de datum te genereren, deze heb je 3x in je formule:
MAX( DATUM($E$3;1;1) ; DATUM($E$3;1;1) -WEEKDAG( DATUM($E$3;1;1) ;2)+(J$5-1)*7+8)

De M variant voor DATUM() is #date(<year>, <month>, <day>) waar we voor 'year' refereren naar de kolom waar je jaartal in staat (zorg dat deze kolom het data-type 'Whole Number' heeft:

code:

#date([Year],1,1)

Nu dat je een datum hebt van "1 januari <jaartal>" kun je verder met het tweede deel, de WEEKDAG() formule. In M is dit Date.DayOfWeek(<datum>, <eerste dag van week>)

In Excel gebruik je de optie 2 die maandag als 1e dag geeft, en zondag als 7e dag. In M gebruik je de optie 1 voor maandag als 1e dag, of de formule Day.Monday. Echter, bij M begint een index op 0, en in Excel bij 1. Je Excel formule voor Weekday geeft dus een '1' voor Maandag als 1e dag, maar M geeft een '0', om dit toch een 1 te laten zijn gebruik je Day.Sunday als je eerste dag van de week (Sunday is dan 0, Monday is dan 1, etc).

Je WEEKDAG in M is dan als volgt:

code:

Date.DayOfWeek(#date([Year],1,1),Day.Sunday)

Nu wil je in het 2e deel van je MAX formule de weekdag van de datum aftrekken, en daarna het weeknummer (minus 1) maal 7 plus 8 bij je datum optellen. Zorg dat ook je kolomo Week het datatype 'Whole Number' heeft

In M gebruik je hiervoor Date.AddDays(<datum>, <aantal dagen>) echter om dagen af te trekken gebruik je gek genoeg ook 'AddDays' maar begin je met een minus karakter.

Dus:
Date.AddDays(<datum, die we hierboven al hebben staan>, - (minus) je WEEKDAG + (plus) je Week-1 maal 7 + 8

Oftewel:

code:

Date.AddDays( #date([Year],1,1), - Date.DayOfWeek(#date([Year],1,1),Day.Sunday) + ([Week]-1) * 7 + 8)

Vervolgens wil je met MAX de grootste vinden tussen 1 januari, of je berekende datum. In M is er geen MAX equivalent (voor zover ik weet), en om niet moeilijk te doen met List.Max (waarbij je eerst lists moet maken met je twee waardes) kun je het makkelijkst een IF statement gebruiken. Want MAX(a, b) is niets anders dan "als a groter is dan b, dan a, anders b". In M werkt de if ook iets anders. Namelijk:

code:

if a > b then a else b

Waar 'a' je datum is die je in het begin genereert, en b de datum die je in het tweede stuk genereert.

Tot slot wil je de boel als "YYYY-MM" weergeven. Echter is dat 'text', en M vind het niet leuk dat je een datum combineert met text, dus je zult eerst de boel moeten omzetten naar text.

Om het jaar te krijgen doe je Date.Year(<hier je formule voor de datum>) en die wrap je in Text.From() om er text van te maken. Dan doe je &"-"& om een streepje toe te voegen. En dan doe je hetzelfde voor de maand met Date.Month(<hier je formule voor de datum>) die je ook wrapped in Text.From()

Echter geeft het resultaat nu de maand zonder leading 0 weer. Hiervoor kun je de maand dus weer wrappen in Text.PadStart(<je tekst>, <aantal karakters>, <karakter wat je toe wilt voegen>)

Met als eindformule (aan de experts; dit kan vást korter ):

spoiler:

Text.From(Date.Year(if #date([Year],1,1) > Date.AddDays(#date([Year],1,1),-Date.DayOfWeek(#date([Year],1,1),Day.Sunday)+([Week]-1)*7+8) then #date([Year],1,1) else Date.AddDays(#date([Year],1,1),-Date.DayOfWeek(#date([Year],1,1),Day.Sunday)+([Week]-1)*7+8)))&"-"&Text.PadStart(Text.From(Date.Month(if #date([Year],1,1) > Date.AddDays(#date([Year],1,1),-Date.DayOfWeek(#date([Year],1,1),Day.Sunday)+([Week]-1)*7+8) then #date([Year],1,1) else Date.AddDays(#date([Year],1,1),-Date.DayOfWeek(#date([Year],1,1),Day.Sunday)+([Week]-1)*7+8))),2,"0")

Echter raad ik je wel aan om gewoon lekker met hulpkolommen te werken in Power Query, nadat je de maand hebt berekend kun je deze kolommen gewoon verwijderen waarmee je berekening blijft staan. Dat scheelt je één grote formule zoals hierboven, en je kunt makkelijker aanpassingen maken en kolommen hergebruiken in je formule in plaats van dat je stukken formule moet hergebruiken.

[GRDavies75]

@Belindo Hele uitgebreide uitleg, maar volgens mij denk je te moeiljik

pseudocode hier, maar iets in de trant van:

code:

Text.From([Jaar]) & "-" & Text.From (
    Date.Month (
    Date.AddWeeks(
        Date.StartOfWeek(#date([Jaar], 1, 1)),
        [Weeknummer] - 1
    )
    )
)

[Belindo]

Ah, AddWeeks, helemaal vergeten, haha!

[MightyMike95]

Hi @GRDavies75

Als ik jmet jouw code meeschrijf in Power Query krijg ik een error. Ik denk zelf dat het ligt aan de #date? Want ik heb nog geen datum. Alleen een weeknummer en een jaarnummer. Hoe krijg ik de error weg?

Text.From([Prognose Jaar]) & "-" & Text.From (
Date.Month (
Date.AddWeeks (
Date.StartOfWeek (#date ([Prognose Jaar],1,1)),
[Prognose week ingediend.1] -1
)
)
)

[GRDavies75]

#date(jaar, maand, dag) produceert een gegeven van het type datum, en dat is wat je moet meeggeven met de functie Date.StartOfWeek, dus dat zou het probleem niet moeten zijn.
Als je een foutmelding krijgt, is het misschien wel zo handig om deze te melden, heel lastig om zonder de melding te bepalen:
wat de fout is?
Waar vind deze plaats?
(En hoe dus op te lossen)

Ik ben ook maar een beginnende powerquery gebruiker en maak intensief gebruik van de volgende link, misschien handig om deze te bookmarken:
https://docs.microsoft.co...uery-m-function-reference

Ik weet wel dat de taal M hoofdletter gevoelig is en vrij strict met z'n gegevenstypes, maar ik vermoed dat je een haakje teveel hebt (voor de laatste komma) bij
Date.StartOfWeek (#date ([Prognose Jaar],1,1)),

Dat 2e haakje voor de komma sluit de functie StartOfWeek af, terwijl die nog een aantal weken verwacht (oftewel het "[Prognose week ingediend.1] -1" gedeelte).

[ Voor 16% gewijzigd door GRDavies75 op 06-05-2022 09:39 ]

[MightyMike95]

@GRDavies75

Je hebt gelijk, ik zag alleen een error in de kolom. Ik ben vergeleken jou denk ik helemaal een beginnende Power Query gebruiker en wist niet dat je op een andere manier ook de error kan laten zien.

In jouw eerste formule miste je 1 stukje (, Day.Sunday)(zie onderaan).

Ik heb eigenlijk precies wat ik nodig heb, maar mis toch net iets sulligs. In de ene kolom het resultaat staan als 2022-02, en uit jouw formule krijg ik 2022-2. Hierdoor kan ik net niet verticaal zoeken. Hoe kan ik ervoor zorgen dat

Text.From ([Prognose Jaar]) & "-" & Text.From (
Date.Month (
Date.AddWeeks (
Date.StartOfWeek (#date ([Prognose Jaar],1,1),Day.Sunday),
[Prognose week ingediend.1] -1)
)
)

[pagani]

Ander dingetje: Besef je altijd bij het werken met weeknummers dat die internationaal verschillen.

[GRDavies75]

Dat is een optionele parameter om aan te geven welke dat je als eerste van de week ziet.
Laat je die weg, dan wordt de zogeheten locale gebruikt (oftewel dit soort standaarden, waaronder eerste dag van de week, wat is het standaard formaat van een datum etc etc.
In Windows als je op het startmenu klikt en type regio, dan kan je dit soort instellingen aanpassen.
Bij powerquery kan je per sheet ook dit soort instellingen instellen voor deze specifieke sheet.

Maar mooi dat het nu voor je werkt.

pagani schreef op vrijdag 6 mei 2022 @ 09:51:
Ander dingetje: Besef je altijd bij het werken met weeknummers dat die internationaal verschillen.

Zie bovenstaand commentaar.
Daarom werk ik het liefst via de locale en als ik weet wat de bron gebruikt als locale die instellen op sheet-basis.

[ Voor 26% gewijzigd door GRDavies75 op 06-05-2022 09:54 ]

[MightyMike95]

En hoe kan ik er dan voor zorgen dat ik het neerzet zoals onderstaand?

Ik heb eigenlijk precies wat ik nodig heb, maar mis toch net iets sulligs. In de ene kolom het resultaat staan als 2022-02, en uit jouw formule krijg ik 2022-2. Hierdoor kan ik net niet verticaal zoeken. Hoe kan ik ervoor zorgen dat

[GRDavies75]

MightyMike95 schreef op vrijdag 6 mei 2022 @ 09:54:
En hoe kan ik er dan voor zorgen dat ik het neerzet zoals onderstaand?

Ik heb eigenlijk precies wat ik nodig heb, maar mis toch net iets sulligs. In de ene kolom het resultaat staan als 2022-02, en uit jouw formule krijg ik 2022-2. Hierdoor kan ik net niet verticaal zoeken. Hoe kan ik ervoor zorgen dat

Meedere wegen naar Rome, maar ik denk dat het relatief het makkelijkst is om een extra stap toe te voegen
En dan checken op de Lengte van het veld, als het maar 6 lang is, dan Text.Replace "-" naar "-0"
Maar ik adviseer je dit om met behulp van de referentielink zelf op te lossen
- Voeg extra kolom toe (aangepaste kolom)
- een if then else op basis van Text.Length
- Text.Replace
Door er zelf mee te stoeien, de foutmelding goed lezen, daar leer je meer van dan dat je kopieert en plakt van wat ik je voorkauw. Je bent er bijna.

Edit: En zo zie je maar, het is altijd veels te makkelijk om moeilijk te denken. In plaats van:
Text.From([Jaar]) & "-" & Text.From (Date.Month....)
Kan je dat gedeelte om omschrijven met:

code:

=Date.ToText(
Date.AddWeeks (
Date.StartOfWeek (#date ([Prognose Jaar],1,1),Day.Sunday),
[Prognose week ingediend.1] -1), "yyyy-MM")

[ Voor 19% gewijzigd door GRDavies75 op 06-05-2022 10:20 . Reden: Vooruit dan maar :) ]