Adding Year data to a table of month names

[p45cal]

I'd like some M-code suggestions (mine is a real mess!) on how to add year data please.

This is the starting point; a singe column table of 3 letter month names. All we know about this is that the month names are in chronological order, latest last, and that the bottom month name is in the current year:

Where there are two or more similar month names next to each other they are each a year apart, so the result I'm looking for is like:

I'm using Excel Power Query, see attached Excel workbook. Thanks.

[v-hjannapu]

Hi @p45cal ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[p45cal]

More clutter.

[lbendlin]

@RieMerritt fyi

[ronrsnfld]

I tried to PM him but received no response. 

[MarkLaf]

Couldn't help but add another method, which I think performs comparitively quite well. Using custom comparer to group the months and then adding Years with an index column. I precomputed the comparison as I couldn't quite figure out how to get it working directly in the comparer argument of Table.Group. Fundamentals of grouping technique are somewhat explained here: https://powerquery.how/table-group/

let
    Source = MonthTable,
    MonthNo = List.Transform(
        Source[MonthNames],
        each List.PositionOf({"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}, _)
    ),
    NextMonthNo = List.RemoveFirstN(MonthNo),
    Compare = List.Transform(List.Zip({MonthNo, NextMonthNo}), each Number.From(_{0} >= _{1})),
    CompareShifted = {null} & List.RemoveLastN(Compare),
    AddCompareCol = Table.FromColumns(
        {Source[MonthNames], CompareShifted}, type table [MonthNames = text, Compare = Int64.Type]
    ),
    GroupByCompare = Table.Group(
        AddCompareCol,
        {"Compare"},
        {{"MonthGroup", each Table.SelectColumns(_, "MonthNames"), Value.Type(Source)}},
        GroupKind.Local,
        (x, y) => y[Compare]
    ),
    AddYears = Table.ReverseRows(
        Table.AddIndexColumn(
            Table.ReverseRows(GroupByCompare), "Years", Date.Year(DateTime.LocalNow()), -1, Int64.Type
        )
    ),
    RemoveCompareCol = Table.SelectColumns(AddYears, {"MonthGroup", "Years"}),
    ExpandMonthGroup = Table.ExpandTableColumn(RemoveCompareCol, "MonthGroup", {"MonthNames"}, {"MonthNames"})
in
    ExpandMonthGroup

[lbendlin]

"Borrowing" some of your ideas

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzEPTLulJoFp38QiFHXo4o6l6Sg0inwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthNames = _t]),
    Months = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Year", each if [Index]= 0 then 1 else if List.PositionOf(Months,[MonthNames])>List.PositionOf(Months,#"Added Index"{[Index]-1}[MonthNames]) then null else 1),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",each [Year],each Date.Year(DateTimeZone.UtcNow()) - (List.Sum(Table.SelectRows(#"Added Custom",(k)=>[Index]<k[Index])[Year])??0),Replacer.ReplaceValue,{"Year"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"MonthNames", "Year"})
in
    #"Removed Other Columns"

[v-hjannapu]

Hi @p45cal,
Yes, that approach uses the current year as a starting point and then checks where the month order goes backward to subtract the year. It avoids looping with List.Accumulate or List.Generate, which makes it simpler in some cases. The ??0 part just helps avoid errors if there's no previous value.

Hope this helps if you have any queries we are  happy to assist you further.
Thank you

[v-hjannapu]

Hi @p45cal,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[p45cal]

Please, stop.

[p45cal]

Thanks to all who responded!
@lbendlin's use of List.Accumulate is straightforward, using it as intended to produce a single value rather than pressing it to return a list (as I did in a messy way).
If I've read it right, @lbendlin uses List.Accumulate once for each row of the table which makes me ask if it might not be comparitively resource-hungry.
Looking at @ronrsnfld's code I see he uses List.Generate, and if I read it right, he uses this only once to generate a list.

I've learnt a fair bit from this thread about List.Accumulate and List.Generate and their beyond-the-basics use. For example, if you're using a record as the initial value in List.Generate, you can refer to a field/value in two ways; in this snippet:

= List.Generate(
() => [x=0,yr= Date.Year(DateTime.FixedLocalNow()) ],
each [x] < max,
each [yr=try if MnthNos{x}>=MnthNos{[x]} then [yr]-1 else [yr] otherwise [yr],x= [x]+1],
each [yr]
)

Note the part
if MnthNos{x}>=MnthNos{[x]}
where x and [x] appear to refer to the new and previous versions of x.
Of course, one could use x and x-1 instead. Is one way more efficient than the other?
Also, I haven't tested this, but I'd guess that the same applies to List.Accumulate.

I'm pleased that the experts here used a similar algorithm to my own for the whole process and that there doesn't seem to be super elegant one-liner to do it!

Anyway, where I've got so far (I've not tried unduly to reduce the step count):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzEPTLulJoFp38QiFHXo4o6l6Sg0inwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthNames = _t]),
MnthNos= List.Reverse(List.Transform(Source[MonthNames],each Date.Month(Date.From(_ & "-1")))),
max = List.Count(MnthNos),
zzz = List.Generate(
() => [x=0,yr= Date.Year(DateTime.FixedLocalNow()) ],
each [x] < max,
each [yr=try if MnthNos{x}>=MnthNos{[x]} then [yr]-1 else [yr] otherwise [yr],x= [x]+1],
each [yr]),

Result = Table.FromColumns({Source[MonthNames]} & {List.Reverse(zzz)},type table[MonthNames=text, Year=Int64.Type])
in
Result

Separately, @ronrsnfld, you say:
"It seems to execute more rapidly."
Do you have a way of timing execution?

Edit: To Stephane/ @slorin , Your reply appeared while I was composing this message - thank you, I'll come back later.

[slorin]

@p45cal 

Une possibilité encore plus directe

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzEPTLulJoFp38QiFHXo4o6l6Sg0inwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthNames = _t]),
Transform = List.Transform(
   List.Zip({
      List.RemoveLastN(Source[MonthNames],1),
      List.RemoveFirstN(Source[MonthNames],1)}),
   each Date.From("1 " & _{0}) >= Date.From("1 " & _{1})),
Year = List.Accumulate(
   List.Reverse(Transform),
   {Date.Year(Date.From(DateTime.FixedLocalNow()))},
   (s,c)=> s & {List.Last(s) - Number.From(c)}),
Result = Table.FromColumns({Source[MonthNames],List.Reverse(Year)},{"MonthNames", "Years"})
in
Result

Stéphane 

[ronrsnfld]

In the PQ (PBI) UI, go to the tools tab.

Diagnostics Start

Then "Refresh All"

Diagnostics Stop

It will generate a report with a number of parameters. If you group by Query and sum the Exclusive Execution Time, you can get an idea.

(It is most useful for seeing which individual steps take the most time in a given query. So to answer your other question about efficiency of the two `x` methods, you can test it both ways and check the timing)

[v-hjannapu]

Hi @p45cal,

Thank you  for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @lbendlin , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided by @lbendlin Please feel free to contact us if you have any further questions. If the response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Regards,

Harshitha.

[p45cal]

I think this kind of message appears too soon and too frequently, to the point that some threads have two or three messages related to the subject matter and then follows a series of 5, 6 more similar messages when it's obvious that the OP has long wandered off into the sunset never to return unless he needs more help.

This means that those trawling the site for meaningful responses have to wade through masses of such messages. I feel there is too much clutter.

Sure, if there's no feedback from the OP after a week, post one such message as a nudge, and if there's still no response a moderator can mark as an answer those responses he feels merit it.

ps. I don't mind in the slightest if this message is deleted for being off topic - perhaps I should raise it elsewhere?

[ronrsnfld]

I agree 100%. My thought is that these should be posted as PM's to the OP and not pollute the various threads at all. I have raised this issue with one the ?directors? of the super user group, as well as with the individual posters in PM's, to no avail. If you have any other ideas, I would be happy to help. Maybe if enough of us ... ???

[slorin]

Hi @p45cal 

Another solution with List.Accumulate

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzEPTLulJoFp38QiFHXo4o6l6Sg0inwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthNames = _t]),
#"Month number" = Table.AddColumn(Source, "MNo", each Date.Month(Date.From("1 " & [MonthNames] & "2000"))),
Transform = List.Transform(
    List.Zip({
       List.RemoveLastN(#"Month number"[MNo],1), 
       List.RemoveFirstN(#"Month number"[MNo],1)}), 
    each _{0}>=_{1}),
Year = List.Accumulate(
    List.Reverse(Transform), 
    {Date.Year(Date.From(DateTime.FixedLocalNow()))}, 
    (s,c)=> if c then s&{List.Last(s)-1} else s&{List.Last(s)}),
Result = Table.FromColumns({Source[MonthNames],List.Reverse(Year)},{"MonthNames", "Years"})
in
Result

Stéphane 

[ronrsnfld]

You can try this method using List.Generate and working from the bottom up. It seems to execute more rapidly.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzEPTLulJoFp38QiFHXo4o6l6Sg0inwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthNames = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MonthNames", type text}}),
    
    #"Add Year Column" = 
            [a=Table.RowCount(#"Changed Type"),
             b=List.Generate(
                 ()=>[x=Date.FromText("1-" 
                            & #"Changed Type"[MonthNames]{a-1} 
                            & "-" & Text.From(Date.Year(DateTime.FixedLocalNow()))), 
                        y=x,
                            idx=a-1],
                 each [idx]>=0,
                 each [x=Date.FromText("1/" & 
                                    #"Changed Type"[MonthNames]{[idx]-1} & "/" 
                                    & Text.From(Date.Year([y]))), 
                        y=if x<[y] then x else Date.AddYears(x,-1) , idx=[idx]-1],
                 each [y]),
             c=List.Reverse(b),
             d=List.Transform(c, each Date.Year(_)),
             e=Table.FromColumns(
                 {#"Changed Type"[MonthNames]}
                 & {d},type table[MonthNames=text, Year=Int64.Type]
             )][e]
in
    #"Add Year Column"

[p45cal]

Update. In the attached Excel workbook is M-code to do this but I was looking for something more elegant. I've probably used a very inefficient way to do this. The resulting table is the green one at cell J2.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzEPTLulJoFp38QiFHXo4o6l6Sg0inwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MonthNames = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Descending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "MNo", each Date.Month(Date.From("1 " & [MonthNames] & "2000"))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each List.Accumulate({0..#"Added Custom"{0}[Index]-[Index]},Date.Year(Date.From(DateTime.FixedLocalNow())),(s,c)=>try if #"Added Custom"{c}[MNo]<#"Added Custom"{c-1}[MNo] then s else s-1 otherwise s),Int64.Type),
    #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows1",{"MonthNames", "Year"})
in
    #"Removed Other Columns"

[p45cal]

Thank you @lbendlin, I'll have a study of your list.accumulate step, but tomorrow, bedtime here now!