power query

Hello VAMONIE 

It is an interesting case, I made a test which is working, maybe there is an elegant way to achieve that, but I will follow this post to see that

First I would copy your table in Power query and make a group by

Now in your first table

 you have to pivot your artikel column

To ge the following table

Then, you have to make a merge between your 2 tables

Your expand the total from the other table and you can delete the amount from your source table

and I should get the results you need

There is maybe a most efficient way, so I would interested to see it 🙂

Hi VAMONIE, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtIzNNAzMjAyVdJRCkgtKs7PU3AEMn0Ob8sDUpZAbGpgoBSrg1OlU35eaTFUqaEBfrWuFSVFiUDaDIiNcSt1ItoBTiQ4wIk0BwQXpCZnJuZAFZuYwhUbEh1c2FTici02tThci64Ud3BhU0msA/AEFzalWIIrFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Datum = _t, Name = _t, Artikel = _t, Units = _t, Amount = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Units", type number}, {"Amount", type number}, {"Datum", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Datum", "Name"}, {{"T", each 
        [ a = Table.RemoveColumns(_, {"Amount"}),
          b = Table.Pivot(a, List.Distinct(a[Artikel]), "Artikel", "Units"),
          c = Table.AddColumn(b, "Amount", (x)=> List.Sum([Amount]), type number)
        ][c], type table}}),
    ComibnedT = Table.Combine(GroupedRows[T])
in
    ComibnedT

Hi VAMONIE 

Another possibility

let
Source = Your_Source,
Artikel = List.Distinct(Source[Artikel]),
Group = Table.Group(Source, {"Datum", "Name"}, 
   {{"Data", each Table.FromRows({[Units]}, [Artikel])},
    {"Amount", each List.Sum([Amount]), type nullable number}}),
Expand = Table.ExpandTableColumn(Group, "Data", Artikel, Artikel)
in
Expand

Stéphane

Hi VAMONIE , another way to look at it. I'll attach the images of the output and M code used to achieve this. Thanks!