If contains a list of values

[Maryurs]

Hello.

I have a column that contain a list composed of year_week:24_45,24_46.....24_52,25_01,25_02......25_52. I need to add a column in wich i shoud have Year_month. For all the cells between 24_45 to 24_48 on the new column to be 24_november, for all the cells between 24_49 to 24_52 to be 24_decembre and so on . I can do it for each value but that means to make 52 lines for 2024 and 52 for 2025...

is there a way to write a code : for each value :24_45, 24_46,24_47, 24_48 on the new column, i shoud have 24_november, for each value 24_49,24_50,24_51,24_52 to be 24_december, this way i should write only 12 lines for 2024 and 12 for 2025

Could you please help me?

[ronrsnfld]

You need to supply more information:

 1. What is your definition of a week?

      For example, the ISO standard has week 1 beginning on the Monday of the first week of the year that includes Thursday.  So the Day 1 of 25-01 would be 30-Dec-2024.

  2. Since some weeks will include dates from more than one month, or more than one year, you need to define how you will derive the appropriate month from the year-week string in those instances.

In other words, should 25_01 be translated to 24_decembre (based on the first day of the week) or some other algorithm?

[Maryurs]

The week starts Monday and has 5 working days: monday to friday. the week belongs to the month in wich are minimum 3 working days. If a week has 2 days in a month and 3 in another, the week belongs to the month with 3 days

Week 31 for exemple starts 29 july-monday and ends 2 august-friday2. 29,30 and 31 are in july and 1 and 2 are in august. so the week belongs to july. July has 5 weeks: 27 to 31, august has 4 : 32 to 35

[ronrsnfld]

And what is the first week of the year?

Is it the week including Jan 1?

The week including Jan 4? (ISO week)

The week including the first Monday of the year?

Something else?

[Maryurs]

if 1 jan is thursday or friday, the 1'st week will be the tha week after-4 or 5 jan, if 1 jan is in the first 2 days of the week, then that is the first week

[ronrsnfld]

And what if 1 Jan is on Wednesday (or Saturday or Sunday)?

[Maryurs]

it's the same 3 working day rule. in 2025 1 jan is wednesday, so the 1'st week of 2025 starts on 30 dec 2024.if 1 jan is thursday to sunday, 1'st week will start the next monday

[ronrsnfld]

That seems to be saying something similar to the standard ISO week-numbering rules which I have read more succinctly stated as:

• Week starts on Monday
• First week of the year includes January 4th

However, the slight difference is that, with your stated requirement for Jan1, then:

• First week of the year includes January 5th.

Here is one method.

Create a date table that conforms to this modified ISO Week-numbering system, and contains the relevant columns.

I name'd this query ISO_DateTable

//Adapted from https://gorilla.bi/power-query/create-iso-week-and-iso-year/
// with df: First week of year includes January 5th

let
    startDt = #date(2024, 1, 1),
    endDt = #date(2026, 12, 31),
    
    dates = List.Dates(startDt,Duration.Days(endDt - startDt) +1, #duration(1,0,0,0)),

    currWed = List.Transform(dates, each Date.StartOfWeek(Date.AddDays(_,2),Day.Wednesday)),
    yrCurrWed = List.Transform(currWed, each Date.Year(_)),
    firstWed = List.Transform(yrCurrWed, each Date.StartOfWeek(#date(_,1,7), Day.Wednesday)),

    isoWeekNumber = List.Transform(List.Zip({currWed,firstWed}), each Number.IntegerDivide(Duration.Days(_{0} - _{1}),7)+1),
    isoYear = List.Transform(List.Zip({dates, isoWeekNumber}), each Date.Year(Date.AddDays(_{0}, 26-_{1}))),
    Weekday = List.Transform(dates, each Date.DayOfWeek(_,Day.Monday)),
    month = List.Transform(dates, each Date.Month(_)),
            
    dateTable=Table.FromColumns(
        {dates}
        & {isoWeekNumber}
        & {isoYear}
        & {month}
        & {Weekday},
        type table[Date=date, modISO_Week_Number=Int64.Type, modISO_Year=Int64.Type, Month=Int64.Type, Weekday = Int64.Type]
    )
in
    dateTable

Date Table

Now you can refer to that table in your code.

In the code below, note that the first five code lines exist merely to create a sample table, since you didn't see fit to share one that could be copy/pasted.

The lines after that reference and buffer the date table, and then do the magic in the Add.Column function:

let

//these next lines are just to create a sample table
//replace them with your own data source
    weeks = List.Transform(List.Numbers(1,52),each Number.ToText(_,"00")),
    years = {"24","25"},
    allWeekYears= Table.FromColumns(
       {years}
        & {{weeks}& {weeks}},
        type table[year=text, week={text}]),
    #"Expanded week" = Table.ExpandListColumn(allWeekYears, "week"),
    Year_Week = Table.CombineColumns(#"Expanded week",{"year","week"},Combiner.CombineTextByDelimiter("_"),"Year_Week"),
 
//read in and buffer the Date Table
    dateTable = Table.Buffer(mod_ISO_DateTable),

//Add the Year_Month column
    #"Add Year_Month" = Table.AddColumn(Year_Week, "Year_Month", (r)=>
        let 
            split = Text.Split(r[Year_Week],"_"),
            dates = Table.SelectRows(dateTable, 
                        each [modISO_Year]=(2000 + Number.From(split{0})) 
                        and [modISO_Week_Number]=Number.From(split{1})
                        and [Weekday] < 5),
            filterMonths = Table.SelectRows(dates, each [Month] = List.Mode(dates[Month])),

//Change culture according to your desired culture settings
            yrMnth = Date.ToText(filterMonths[Date]{0},"yy_MMMM","ro-RO")
            in 
                yrMnth, type text)
in
    #"Add Year_Month"

Result

If this doesn't work, please supply data that can be copy/pasted -- text and NOT a screenshot -- that can reproduce the issue.

[Maryurs]

I don't get it. why create another column with year, week number...i alreadei have them.in my data table i already have the column with the year_week .it isn't just a list of week's. i heve an extraction from an program with multiple columns, the last is year_week. but a week apears on multiple rows. the same year_week may apear more than 100 times. I have this:

= Table.AddColumn(#"Added Custom Column", "Month", each if [Saptamana] = "24_49" then "24 Decembrie" else if [Saptamana] = "24_50" then "24 Decembrie" else if [Saptamana] = "24_51" then "24 Decembrie" else if [Saptamana] = "24_52" then "24 Decembrie" else null)

 but for each 24_week i must i must write:[Saptamana] = "24_50" then "24 Decembrie". I need the code like := Table.AddColumn(#"Added Custom Column", "Month", each if [Saptamana] = "24_49"or"24_50" or"24_51" or "24_52" then "24 Decembrie" else.... 

[ronrsnfld]

Read the comments in the code. I'm not adding anything to your table except the Year-Month column. Since you chose to not provide a sample data table to work with, I was obliged to create one that I could work with.

The relevant part is to add the year-month column, which is what you said you wanted. It refers to a Year-Week column, which I think you have named Saptamana.

[wdx223_Daniel]

NewStep=Table.AddColumn(#"Added Conditional Column", "mois", each List.Skip({{{"24_01","24_02","24_03","24_04"},"Jan 2024"},{{"24_05","24_06","24_07","24_08"},"Feb 2024"}},each not List.Contains(_{0},[Saptamana])){0}{1})

you can expand the list in the first argument of List.Skip with the format of {{{weeklist},month1},{{weeklist},month2}}

[Maryurs]

Hello,

 I don't understand where is the problem

[wdx223_Daniel]

the first arguement of Table.AddColumn should your raw data table name, i.e. the last step name in the right-hand pane

[PwerQueryKees]

By the way, there is a List.ContainsAny, but ths still leave you with 12 lines. In any case a lookup table is your way to go. If you want to keep is simple, you can just create a little table with your year_week combinations and fill in the month manually. Not pretty, but it will work.

[PwerQueryKees]

OK. The way I understand it

• your Saptmana column has a year and a week number, separated by _
• You want to have the month the weeknumber falls in

I would not try to list all year_week combinations in your formula. Hard to maintain...

First split the column by delimiter _

This will give you a year and a weeknumber column to start with

In general, the weeknumbers are not standard globally and sometimes not even nationally. 

I assume you now the standard you want to follow.

This makes writing a function that just calculates the monthnumber rather hard.

You can have a look at https://learn.microsoft.com/en-us/powerquery-m/date-startofweek but there is a good chance that it will not fit your requirements.

Alternatively, appraoch would be:

• Generate a table with a list of dates with List.Generate() and the convert into a table.
• Split the dates in day month year (all as numbers)
• Calculate the weeknumbers using https://learn.microsoft.com/en-us/powerquery-m/date-weekofyear, this gives you options to use the corect standard.
• Group by Year and weeknumber and the minimum data (i.e. first day of that week)
• Do a merge-join with your original  table
• Use https://learn.microsoft.com/en-us/powerquery-m/date-totext to get the name of the month of the first day of the week.

[Maryurs]

This will not work because the repartition of the weeks in months is not by date. if the 1'st of a month is wednesday, the week is in that month, if is monday or tuesday, the week belongs in the previous month. For exemple, 1 may 2024 is in week 18 and is wednesday so the week 18 is in may, but 1 august 2024 is in week 31 but is Thursday, so the week 31 belongs to jully...

[Ahmedx]

Is this what you are looking for?

[Omid_Motamedise]

I do not catch your question, can you explain it by an example?

[PwerQueryKees]

please share (some of) your actual data, the M Code you already got and a mock up of the desired result as a data table, so I can understand what it is you want.

[Maryurs]

Hello,

This is an image with what i have now and it works.But this way i must "write" 52 lines for each week of the year 24 and another 52 for year 25. is there a way to add multiple conditions in one ligne?

Like: Table.AddColumn(#"Added Conditional Column", "mois", each if Text.Contains([Saptamana], "24_45,24_46,24_47,24_48")
then "Noi 2024" else "XXX")