M Query Formula to bring Previous Month Segment

Hi Anonymous ,

Open "Advanced Editor" and copy and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lFyBOLgxJzUYqVYHRQJJxQJI1QdjsnJ+aV5JRhyqJqMERLOQOybWJSdWpKZl44u6YIhaYKqE8U6E5waYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parse = _t, #"Emp code" = _t, Segment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parse", type date}, {"Emp code", type text}, {"Segment", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Emp code"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Parse", "Segment", "Index"}, {"Parse", "Segment", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Index]<>1 then List.Min(Table.SelectRows(#"Expanded Count",(x)=>x[Emp code]=[Emp code] and x[Index]=[Index]-1)[Segment]) else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let _maxindex=List.Max(Table.SelectRows(#"Added Custom",(x)=>x[Emp code]=[Emp code])[Index])
in if [Custom]=null then 0 else if [Index]=_maxindex and [Custom]=[Segment]  then 0 else if  [Index]=_maxindex and [Custom]<>[Segment] then 1 else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each let _maxindex=List.Max(Table.SelectRows(#"Added Custom",(x)=>x[Emp code]=[Emp code])[Index]),
_count=Table.RowCount(Table.SelectRows(#"Added Custom1",(x)=>x[Emp code]=[Emp code] and x[Custom.1]=1))
in if [Index]<>_maxindex and _count>0 then -1 else [Custom.1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index", "Custom", "Custom.1"})
in
    #"Removed Columns"

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.