Ik heb even iets geprobeerd voor je en zal het proberen stap voor stap uit te leggen. De aanpak is als volgt:
- Je weet de start en eind datum en wilt eigenlijk alle tussenliggende datums weten om een weeknummer toe te passen
- Je moet dan het aantal rijen zo vaak dupliceren als dat je 'datums' hebt
- Vervolgens pas je een weeknummer toe aan elke datum
- Tevens kijk je of de datum een werkdag is (aanname: Mon-Fri)
Je begint met de tabel die je al hebt, al zijn 'Werkweek' en 'Werkdagen ziek' hier niet relevant:
:fill(white):strip_exif()/f/image/gKBtErpg6B3e12lfimVQplEN.png?f=user_large)
Vervolgens selecteer je die tabel, en ga je in Excel naar Data > from Table/Range. Je opent dan de Power Query Editor:
:fill(white):strip_exif()/f/image/NiT35KPvTbxVbivnid6YDJQW.png?f=user_large)
De eerste stap binnen je nieuwe query is het aanpassen van de datatypes. De Start en Einddatum worden standaard (bij mij) gezien als DateTime, maar deze willen we als Date hebben. De andere kolommen kun je het format Text of Number geven, na deze stap ziet je tabel er zo uit (let op die icoontjes in de kolomheaders, dit is je datatype):
/f/image/eLl1MCnmLzOnuQ3jpcpKTg8u.png?f=fotoalbum_large)
Nu kunnen we binnen de query een nieuwe kolom toevoegen (Add Column > Custom). Deze kolom gaat uiteindelijk een list bevatten met alle datums tussen de Start en de Eind datum. Let wel dat je het aantal stappen plus één doet om ook de einddatum in die lijst te krijgen:
/f/image/zCuf5lMtmkyW27hW5AJHP39K.png?f=fotoalbum_large)
De formule bestaat uit 3 delen, hier dikgedrukt:
quote: De startdatum:
List.Dates( [Ziek Start] ,(Int64.From([Beter])-Int64.From([Ziek Start])+1),#duration(1, 0, 0, 0))
quote: De duur (het verschil tussen eind en start, plus één:
List.Dates([Ziek Start], (Int64.From([Beter])-Int64.From([Ziek Start])+1) ,#duration(1, 0, 0, 0))
quote: De increment, dat is 1 dag:
List.Dates([Ziek Start],(Int64.From([Beter])-Int64.From([Ziek Start])+1), #duration(1, 0, 0, 0) )
Je tabel is nu uitgebreid met een kolom met daarin het woord 'List'. Deze list bevat alle datums vanaf start t/m eind. Volgende stap is om deze te 'expanden' naar nieuwe rijen:
:fill(white):strip_exif()/f/image/potyHmJCpvCmXyfCBdfJuABX.png?f=user_large)
Met dit als resultaat:
/f/image/zxQ234Uraqmdkj8wwWlvAUQb.png?f=fotoalbum_large)
Vervolgens doe je 'Close & Load' en je nieuwe tabel wordt ingeladen op een nieuwe sheet.
In deze tabel voeg je twee kolommen toe om te checken of de datum een werkdag is:
quote: Is werkdag TRUE/FALSE:
=WEEKDAY([@[All Dates]],2)<6
En het weeknummer behorende bij die datum:
quote: Weeknummer:
=WEEKNUM([@[All Dates]],21)
Dat ziet er dan zo uit:
:fill(white):strip_exif()/f/image/IoqP2JynQUhuBHbalYIbuvHo.png?f=user_large)
Als laatste stap maak je een pivot op die nieuwe tabel die er bijvoorbeeld zo uitziet:
:fill(white):strip_exif()/f/image/r3Om0BBcFYkikyxHRFawT1li.png?f=user_large)
Ik heb de verschillende velden zo neergezet:
:fill(white):strip_exif()/f/image/vVjYjy31ox3fBXKtdQi8fu0c.png?f=user_large)
:fill(white):strip_exif()/f/image/uojO0XHgvg10oMslaWIh5yTs.png?f=user_large)
:strip_icc():strip_exif()/u/490608/crop5e346de55c670_cropped.jpeg?f=community)
:fill(white):strip_exif()/f/image/eLl1MCnmLzOnuQ3jpcpKTg8u.png?f=user_large)
:fill(white):strip_exif()/f/image/zCuf5lMtmkyW27hW5AJHP39K.png?f=user_large)
:fill(white):strip_exif()/f/image/zxQ234Uraqmdkj8wwWlvAUQb.png?f=user_large)
/u/276077/cerberus.png?f=community)
:fill(white):strip_exif()/f/image/5XsPtVLCgOYosQxtjenTpSlS.png?f=user_large)
:fill(white):strip_exif()/f/image/6hurDJGxUuNVgSwH23Ih1b0w.png?f=user_large)
.:fill(white):strip_exif()/f/image/yXe4m6FKhoK0ZOfLkvO16qmx.png?f=user_large)
:fill(white):strip_exif()/f/image/7BPcw4TT6cUeIydMMiCyiUVw.png?f=user_large){kind=link}