power query

[VAMONIE]

Hi.

I have some data and like to have it all on the same row. This is my example.

When I pivot on Artikel I get this. But I want to have all units in the same row and a sum of column Amount.

This Is what I want but cant get it right. Can anybody try to explaing how to do för a beginner in Power Query, Thanks.

[Cookistador · Accepted solution]

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 🙂

[dufoq3 · Accepted solution]

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

[slorin · Accepted solution]

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

[VAMONIE]

Worked fine, Thanks 

[V-yubandi-msft]

Hi @VAMONIE ,

Has your issue been resolved, or do you require any further information? Your feedback is valuable to us. If the solution was effective, please mark it as 'Accepted Solution' to assist other community members experiencing the same issue.

[V-yubandi-msft]

Hi @VAMONIE ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @VAMONIE ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

Thanks..

[V-yubandi-msft]

Hi @VAMONIE ,

@dufoq3  has provided an accurate solution to your issue. Could you please confirm if it meets your requirements? If you need any further clarification.

If your issue is resolved, kindly mark it as the Accepted Solution. Otherwise, let us know how we can assist you further.

Thank you all for your valuable feedback.

Regards,

Yugandhar.

[SundarRaj · Accepted solution]

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

[slorin · Accepted solution]

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

[dufoq3 · Accepted solution]

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

[lbendlin]

Please don't do that in Power Query. Your original data is in perfect shape.  let the Matrix visual in Power BI do the work for you.

[Cookistador · Accepted solution]

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 🙂