Question about sum

[GeekAlfPro]

Hi,

i've a table with dates, that i managed to clean thanks to @AIB, see here for the original post

Now, i try to add a cumulative sum in the supplied code, but i don't know M language enough to achieve this

here is the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRNb4MwDIb/ysS5EomdBDj2sGma1qmHbtJUcaAt2lD5kGj7/2dSBM7GxKBDsoOR4XHsN2y3nhZKKW/hrZbP5F/S066i9alxgCAgilALQARNT0SXuEry5JClzbPIl9KnTPE9kJoFgbV4MZEoB4jScAgPAG4nwgARkEOcILqdiNfENS3rusqr8iM5Z1V5VwzzUfCGB76ErhdkoeWriC6Klhty9/t9A34tdml9tiUYEBqMptcMALSD3bz9LMG+X17yvC1CDHbafgLlLLL8I7mHSbbj0I6gBWutu5Y/pnXDpLuHOimPFo3CNGgQArT5Tc18X27AhaatzcPK0XG73ICrHQflNr0ImFaEVHwCoXPIr25eGePSd8pwThupoBeClZJqq0DEfrTZ6TMrErp7vxyzVoN0GWjSwGZOFALvBVqbhx38tzm95QGIf8KOj97lSh7wvStrcfwF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Matricule = _t, Regroupement = _t, Nom = _t, Prénom = _t, #"N° SS" = _t, Index = _t, #"Justification (Code)" = _t, #"Justification (Libellé)" = _t, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)" = _t, #"Justif - Date début absence" = _t, #"Justif - Date fin absence" = _t, #"Nb jours sur la période sélectionnée" = _t, #"Nb jours total par arrêt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Matricule", Int64.Type}, {"Regroupement", type text}, {"Nom", type text}, {"Prénom", type text}, {"N° SS", type text}, {"Index", Int64.Type}, {"Justification (Code)", type text}, {"Justification (Libellé)", type text}, {"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", type date}, {"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}, {"Nb jours sur la période sélectionnée", Int64.Type}, {"Nb jours total par arrêt", Int64.Type}}),


    #"Grouped Rows" = Table.Group(#"Changed Type", {"Matricule"}, {{"Grouped", each _, type table [Matricule=nullable number, Regroupement=nullable text, Nom=nullable text, Prénom=nullable text, #"N° SS"=nullable text, Index=nullable number, #"Justification (Code)"=nullable text, #"Justification (Libellé)"=nullable text, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)"=nullable date, #"Justif - Date début absence"=nullable date, #"Justif - Date fin absence"=nullable date, Nb jours sur la période sélectionnée=nullable number, Nb jours total par arrêt=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped],
            colStart_ = inputT_[#"Justif - Date début absence"],
            colEnd_ = inputT_[#"Justif - Date fin absence"],
            S1_ = Table.AddIndexColumn(inputT_,"Index2",0),
            S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index2]-1}=Date.AddDays([#"Justif - Date début absence"],-1) otherwise false then null else [#"Justif - Date début absence"], type date),
            S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else [#"Justif - Date fin absence"], type date),
            #"Filled Up" = Table.FillUp(S3_,{"Date2"}),
            #"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null)
        in
            #"Filtered Rows"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2"}, {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped", "Index2", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date1", "Justif - Date début absence"}, {"Date2", "Justif - Date fin absence"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}})
in
    #"Changed Type1"

at the stage #"added costum" i tried this, but of course, it failed !

= Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped],
            colStart_ = inputT_[#"Justif - Date début absence"],
            colEnd_ = inputT_[#"Justif - Date fin absence"],
            colCumul_ = inputT_[#"Nb jours sur la période sélectionnée"],
            S1_ = Table.AddIndexColumn(inputT_,"Index2",0),
            S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index2]-1}=Date.AddDays([#"Justif - Date début absence"],-1) otherwise false then null else [#"Justif - Date début absence"], type date),
            S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else [#"Justif - Date fin absence"], type date),
            S4_ = Table.AddColumn(S2_, "CumulJour", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else List.Sum({#"Grouped Rows"[Nb jours sur la période sélectionnée], type list})),
            #"Filled Up" = Table.FillUp(S3_,{"Date2"}),
            #"Filled Up Cumul" = Table.FillUp(S4_,{"CumulJour"}),
            #"Filtered Rows2" = Table.SelectRows(#"Filled Up Cumul", each [Date1] <> null),
            #"Filtered Rows" = Table.SelectRows(#"Filled Up Cumul", each [Date1] <> null)

        in
            #"Filtered Rows")

especially this line

  S4_ = Table.AddColumn(S2_, "CumulJour", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else List.Sum({#"Grouped Rows"[Nb jours sur la période sélectionnée], type list})),

thanks for your help

[GeekAlfPro]

Thanks @AlB 

that's exactly what i want.

the syntax of M is very particular, i would'nt have find it alone.

you're very helpfull !

Thank you very much, you save me a lot of time

[AlB · Accepted solution]

@GeekAlfPro 

Place the following M code in a blank query to see the steps. See the S4_ step added at the end of the Added Custom step. That's basically the difference with the previous code.  I'm not very happy about this approach but it does the job for the time being. Not sure if it will impact performance negatively

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRNb4MwDIb/ysS5EomdBDj2sGma1qmHbtJUcaAt2lD5kGj7/2dSBM7GxKBDsoOR4XHsN2y3nhZKKW/hrZbP5F/S066i9alxgCAgilALQARNT0SXuEry5JClzbPIl9KnTPE9kJoFgbV4MZEoB4jScAgPAG4nwgARkEOcILqdiNfENS3rusqr8iM5Z1V5VwzzUfCGB76ErhdkoeWriC6Klhty9/t9A34tdml9tiUYEBqMptcMALSD3bz9LMG+X17yvC1CDHbafgLlLLL8I7mHSbbj0I6gBWutu5Y/pnXDpLuHOimPFo3CNGgQArT5Tc18X27AhaatzcPK0XG73ICrHQflNr0ImFaEVHwCoXPIr25eGePSd8pwThupoBeClZJqq0DEfrTZ6TMrErp7vxyzVoN0GWjSwGZOFALvBVqbhx38tzm95QGIf8KOj97lSh7wvStrcfwF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Matricule = _t, Regroupement = _t, Nom = _t, Prénom = _t, #"N° SS" = _t, Index = _t, #"Justification (Code)" = _t, #"Justification (Libellé)" = _t, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)" = _t, #"Justif - Date début absence" = _t, #"Justif - Date fin absence" = _t, #"Nb jours sur la période sélectionnée" = _t, #"Nb jours total par arrêt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Matricule", Int64.Type}, {"Regroupement", type text}, {"Nom", type text}, {"Prénom", type text}, {"N° SS", type text}, {"Index", Int64.Type}, {"Justification (Code)", type text}, {"Justification (Libellé)", type text}, {"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", type date}, {"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}, {"Nb jours sur la période sélectionnée", Int64.Type}, {"Nb jours total par arrêt", Int64.Type}}),


    #"Grouped Rows" = Table.Group(#"Changed Type", {"Matricule"}, {{"Grouped", each _, type table [Matricule=nullable number, Regroupement=nullable text, Nom=nullable text, Prénom=nullable text, #"N° SS"=nullable text, Index=nullable number, #"Justification (Code)"=nullable text, #"Justification (Libellé)"=nullable text, #"Date d'arrêt initial#(cr)#(lf)(MAL et MALP)"=nullable date, #"Justif - Date début absence"=nullable date, #"Justif - Date fin absence"=nullable date, Nb jours sur la période sélectionnée=nullable number, Nb jours total par arrêt=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let inputT_ =[Grouped],
            colStart_ = inputT_[#"Justif - Date début absence"],
            colEnd_ = inputT_[#"Justif - Date fin absence"],
            S1_ = Table.AddIndexColumn(inputT_,"Index2",0),
            S2_ = Table.AddColumn(S1_, "Date1", each if try colEnd_{[Index2]-1}=Date.AddDays([#"Justif - Date début absence"],-1) otherwise false then null else [#"Justif - Date début absence"], type date),
            S3_ = Table.AddColumn(S2_, "Date2", each if try colStart_{[Index2]+1}=Date.AddDays([#"Justif - Date fin absence"], 1) otherwise false then null else [#"Justif - Date fin absence"], type date),
            #"Filled Up" = Table.FillUp(S3_,{"Date2"}),
            #"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Date1] <> null),
            S4_ = Table.AddColumn(#"Filtered Rows", "Cumul_", (inner1)=> List.Sum(Table.SelectRows(inputT_, (inner2)=>inner2[#"N° SS"] = inner1[#"N° SS"] and inner2[#"Justif - Date début absence"]>=inner1[Date1] and inner2[#"Justif - Date fin absence"]<=inner1[Date2])[Nb jours sur la période sélectionnée]), Int64.Type)   
        in
            S4_),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2", "Cumul_"}, {"Regroupement", "Nom", "Prénom", "N° SS", "Index", "Justification (Code)", "Justification (Libellé)", "Date d'arrêt initial#(cr)#(lf)(MAL et MALP)", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt", "Index2", "Date1", "Date2", "Cumul_"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped", "Index2", "Justif - Date début absence", "Justif - Date fin absence", "Nb jours sur la période sélectionnée", "Nb jours total par arrêt"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date1", "Justif - Date début absence"}, {"Date2", "Justif - Date fin absence"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Justif - Date début absence", type date}, {"Justif - Date fin absence", type date}, {"Cumul_", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Cumul_", "Nb jours sur la période sélectionnée"}})
in
    #"Renamed Columns1"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[GeekAlfPro]

Hi @AlB 

Actually, my next step is that i just want to sum the days in the column [Nb jours sur la période sélectionnée] by the periods you built.

here is the file with the result

Example with Dates and conditionnal sum

Thanks again ! 

[AlB]

Hi @GeekAlfPro 

I don't understand what you are trying to do.

Can you show  the expeted result based on the initial sample provided?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers