PowerQuery Weeknummer omzetten naar Periode

[mortenzz]

Hi,

Zou het fijn vinden als er iemand even mee kan denken. Ben niet helemaal thuis in PowerQuery formules en het lukt me nog niet.

Heb een Query ingeladen in Excel met een datum. Deze heb ik nu omgezet via een Aangepaste Kolom met de formule: =Date.WeekOfYear([Openingsdatum])-1 naar een weeknummer.
Nu zou ik graag dit weeknummer omzetten naar een periode. Via een Voorwaardelijke kolom zou ik dit natuurlijk kunnen doen, maar dan moet ik voor elk weeknummer een regel aanmaken.

Is het ook mogelijk om dit via een Aangepaste Kolom (dus dmv een formule) te doen?

Bv: value.as(weeknummer=1;1) of zoiets.

Week 1 t/m 5 = periode 1
Week 6 t/m 9 = Periode 2
Week 10 t/m 13 = Periode 3

enz. enz.

Heel hartelijk dank!:thumb:

 

[JEC.]

Voor je weeknummer zou ik een andere functie gebruiken. Date.WeekOfYear is niet altijd correct. Zie bijlage. Op 1-1-2023 zie je het al gebeuren.
Heb voor de correcte weeknummers een custom function gebruikt.

De incorrecte_weeknum stap kun je gewoon verwijderen

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Datum", type date}}),
    weeknum = Table.AddColumn(cTypes, "ISO_WeekNum", each ISO_WeekNum([Datum])),
    incorrecte_weeknum = Table.AddColumn(weeknum, "Verkeerde week", each Date.WeekOfYear([Datum])-1),
    bins = {5,9,13},
    labels = {1..List.Count(bins)+1},
    Result = Table.AddColumn(incorrecte_weeknum, "Periode", each let c = List.Count(List.Select(bins, (x)=> [ISO_WeekNum]>x)) in labels{c} )
in
    Result

 

[JEC.]

Dit is een verbeterde versie met al je perioden. Je hebt uiteraard wel de ISO_WeekNum functie nodig uit het hiervoor geplaatste bestand.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Datum", type date}}),
    weeknum = Table.AddColumn(cTypes, "ISO_WeekNum", each ISO_WeekNum([Datum])),
    bins = List.Alternate({5..53},3,1,1),
    labels = {1..List.Count(bins)+1},
    Result = Table.AddColumn(weeknum, "Periode", each let c = List.Count(List.Select(bins, (x)=> [ISO_WeekNum]>x)) in labels{c} )
in
    Result

 

[roeljongman]

Belangrijke vraag is wat bepaald of een periode 5 weken omvat of 4 weken.
Als dit voor een financiele kalender is dan is het ongetwijfeld op basis van dezelfde regels als het weeknummer. Als donderdag de eerste van de nieuwe maand is dan hoort de week bij de volgende periode, anders hoort die nog bij de huidige maand.

Als week 1 tm 5 uit jou startpost op 2023 staat is dat een onlogische keus obv bovenstaande regel. Dus als je een toekomstbestendige query wilt en je niet elk jaar moet gaan aanpassen is het belangrijk te weten wat de (reken)regel is.

 

[mortenzz]

Wat fijn dat er zo veel mensen willen meedenken. Erg bedankt daarvoor.

De verdeling is 5,4,4 5,4,4 5,4,4 5,4,4
Dus periode 1, 4, 7 en 10 bevatten vijf weken, de rest vier.

Wanneer het makkelijker is om een formule te maken waarbij je elk weeknummer toewijst, zoals als weeknummer =1;1;als weeknummer=2;1 enz enz.
In Excel lukt dat zo maar in de query zelf lukt me dat (nog) niet.

 

[JEC.]

Je kunt het in de "bins" stap regelen.

 

[roeljongman]

Als het ieder jaar hetzelfde patroon van 5 en 4 weken patroon is dan is de bins methode van JEC de simpelste oplossing.

 

[mortenzz]

Voor je weeknummer zou ik een andere functie gebruiken. Date.WeekOfYear is niet altijd correct. Zie bijlage. Op 1-1-2023 zie je het al gebeuren.
Heb voor de correcte weeknummers een custom function gebruikt.

De incorrecte_weeknum stap kun je gewoon verwijderen

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cTypes = Table.TransformColumnTypes(Source,{{"Datum", type date}}),
    weeknum = Table.AddColumn(cTypes, "ISO_WeekNum", each ISO_WeekNum([Datum])),
    incorrecte_weeknum = Table.AddColumn(weeknum, "Verkeerde week", each Date.WeekOfYear([Datum])-1),
    bins = {5,9,13},
    labels = {1..List.Count(bins)+1},
    Result = Table.AddColumn(incorrecte_weeknum, "Periode", each let c = List.Count(List.Select(bins, (x)=> [ISO_WeekNum]>x)) in labels{c} )
in
    Result

Geweldig!! Ik snap waarom je Giga Senior bent! Heel erg bedankt.

Heb alleen nog een vervolgvraag.
Hoe kun je die lijsten (Bins, Labels) toevoegen wanneer je de query bewerkt? Dan kan ik het in mijn eigen bestand reproduceren. Edit: Gevonden, via geadvanceerde editor.

 

[mortenzz]

Hi,

Toch nog een vervolgvraag.

Wanneer de bron SalesForce is, wordt de data aangeleverd in een tabel, die je daarna uitvouwt. Wanneer ik in deze query dan verwijs naar zo'n uitgeklapt veld herkent hij dat veld niet.

----
let
Bron = Salesforce.Reports("https://my.salesforce.com/console", [ApiVersion=48]),
#"00O6M000008XEziUAG" = Bron{[Name="00O6M000008XEziUAG"]}[Data],
----

Nu heb ik deze query dus laten inladen in een tabel. Die tabel opnieuw ingeladen en daar dan de periodes aan toegevoegd en deze weer opnieuw ingeladen.

Is er een manier om toch in de originele query dit toe te passen?
Het veld heet overigens "Sluitingsdatum".

Nogmaals dank!

 

[JEC.]

Jou zou even moeten kijken in welke stap het misgaat. De source stap zou je aan moeten passen en de volgende stap waarschijnlijk ook omdat de kolomnaam anders is.

 

[mortenzz]

hi JEC,

Ik heb dus gekeken en het probleem zit hem in het feit dat bij het inladen van de bron, die kolomnaam nog niet bestaat. Pas na het uitvouwen van de tabel vanuit de bron wordt het veld zichtbaar. Maar omdat hij verwijst naar de bron herkent ie de veldnaam dus niet.
Ik dacht, misschien kun je een verwijzing maken naar de data in de bron ipv de bron.
Maar goed, het werkt ook op de andere manier, dus helemaal prima zo.

Bedankt voor je tijd.

 

[JEC.]

Het kan zekerweten in 1x. Eerst uitvouwen en dan verder vanaf de cTypes stap