Time difference and total per day and week and month

[hegler23]

Hallo zusammen,vielleicht kann mir jemand helfen,
meine Tabelle zu vervollständigen, damit ich die täglichen, wöchentlichen, monatlichen Stunden in meiner Tabelle anzeigen kann. Ziel ist es, dass ich die Auslastung der Mitarbeiter darstellen kann.

Ich würde mich freuen, wenn mich jemand unterstützen würde.

Danke und Grüße
hegler

[dufoq3 · Accepted solution]

Hi @hegler23, as @v-junyant-msft  mentioned --> your description is too poor. I would say you are looking for something like this. I've also added duration in hours in case you want to use it in excel (this will be easily convertable to minutes, days etc.)

Result:

Comment1: Tag value = Woche value = Monat value, because your sample contains only 1 date 31.3.2024.
Comment2: Duration is in [dd:hh:mm:ss] format.

let
    fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table =>
        //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ
        let
            a = Table.Column(tbl, col),
            b = if shift = 0 or shift = null then a else if shift > 0
                then List.Repeat({null}, shift) & List.RemoveLastN(a, shift)
                else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1),    
            c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) &
                ( if newColName <> null then {newColName} else
                    if shift = 0 then {col & "_Duplicate"} else
                    if shift > 0 then {col & "_PrevValue"} 
                    else              {col & "_NextValue"} )),
            d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any})
        in
            d,
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc3LCQAhDEXRXrIWfCZ+MmlF7L8Nx4UwkywvHLhzEutQZaFEUjIkM7i+wTCwAbTSNdWbMuywr2nBNBNnejDVmnuNYA74Gw0GwTzeoF+zNg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Auftrag = _t, Datum = _t, Uhrzeit = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Datum", type date}, {"Uhrzeit", type time}}, "de-DE"),
    GroupedRows = Table.Group(ChangedType, {"Datum"}, {{"All", each fnShift(_, "Uhrzeit", 1, null, type time), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_DifDays = Table.AddColumn(CombinedAll, "Dif Days", each Duration.TotalDays(([Datum] & [Uhrzeit_PrevValue]) - ([Datum] & [Uhrzeit])), Int64.Type),
    Ad_DifHours = Table.AddColumn(Ad_DifDays, "Dif Hours", each [Dif Days] * 24, type number),
    Ad_YearMonth = Table.AddColumn(Ad_DifHours, "YearMonth", each Number.From(Date.ToText([Datum], "yyyyMM")), Int64.Type),
    Ad_YearWeek = Table.AddColumn(Ad_YearMonth, "YearWeek", each Date.Year([Datum]) * 100 + Date.WeekOfYear([Datum], Day.Monday), Int64.Type),
    GroupedRowsMonat = Table.Group(Ad_YearWeek, {"YearMonth"}, {{"Monat Duration", each Duration.From(List.Sum([Dif Days])), type number}, {"Monat Hours", each List.Sum([Dif Hours]), type number}, {"All", each _, type table}}),
    ExpandedMonat = Table.ExpandTableColumn(GroupedRowsMonat, "All", List.RemoveMatchingItems(Table.ColumnNames(Ad_YearWeek), Table.ColumnNames(GroupedRowsMonat))),
    GroupedRowsWoche = Table.Group(ExpandedMonat, {"YearWeek"}, {{"Woche Duration", each Duration.From(List.Sum([Dif Days])), type number}, {"Woche Hours", each List.Sum([Dif Hours]), type number}, {"All", each _, type table}}),
    ExpandedWoche = Table.ExpandTableColumn(GroupedRowsWoche, "All", List.RemoveMatchingItems(Table.ColumnNames(ExpandedMonat), Table.ColumnNames(GroupedRowsWoche))),
    GroupedRowsTag = Table.Group(ExpandedWoche, {"Datum"}, {{"Tag Duration", each Duration.From(List.Sum([Dif Days])), type number}, {"Tag Hours", each List.Sum([Dif Hours]), type number}, {"All", each _, type table}}),
    ExpandedTag = Table.ExpandTableColumn(GroupedRowsTag, "All", List.RemoveMatchingItems(Table.ColumnNames(ExpandedWoche), Table.ColumnNames(GroupedRowsTag))),
    RemovedOtherColumns = Table.SelectColumns(ExpandedTag,{"Auftrag", "Datum", "Uhrzeit", "Tag Duration", "Tag Hours", "Woche Duration", "Woche Hours", "Monat Duration", "Monat Hours"}),
    ChangedType2 = Table.TransformColumnTypes(RemovedOtherColumns,{{"Auftrag", Int64.Type}, {"Datum", type date}, {"Uhrzeit", type time}, {"Tag Duration", type duration}, {"Tag Hours", type number}, {"Woche Duration", type duration}, {"Woche Hours", type number}, {"Monat Duration", type duration}, {"Monat Hours", type number}})
in
    ChangedType2

[v-junyant-msft]

Hi @hegler23 ,

I would suggest you to provide your sample data in the form of a screenshot or a table and provide your expected results, you can put your expected results in excel before taking a screenshot, thanks!

Best Regards,
Dino Tao

[hegler23]

[v-junyant-msft]

Hi @hegler23 ,

Can you explain what the data in each column represents and how the calculations were made? And is it possible to attach your expected results? I can't understand what you are hoping to calculate with just the screenshot you provided.

Best Regards,
Dino Tao

[hegler23]

Wie kann ich meine Datei anhängen?