Group and sum delimited values 2

NurlanPPF
Posts: 9
Joined: Tue Oct 23, 2018 10:15 pm

Group and sum delimited values 2

Postby NurlanPPF » Tue Nov 27, 2018 10:53 pm

Hello!

This expression

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

= Table.Group(Source, "ColumnA", {"new", each Text.Combine(List.Transform(List.Zip(List.Transform([ColumnB], each List.Transform(Text.Split(_, ";"), each Number.From(_)))), each Text.From(List.Sum(_))), ";")})

groups and adds numbers in the list of only one column.
pic1.png
pic1.png (30.15 KiB) Viewed 520 times

How to change this expression to sum lists of several columns?
pic2.png
pic2.png (43.92 KiB) Viewed 520 times


Regards,
Nurlan

Rudi
Posts: 14
Joined: Fri Sep 01, 2017 4:33 pm

Re: Group and sum delimited values 2

Postby Rudi » Tue May 21, 2019 9:52 pm

Like this:

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Data = Table.Group(Source, {"ColumnA"},
    {
        {"new", each Text.Combine(List.Transform(List.Zip(List.Transform([ColumnB], each List.Transform(Text.Split(_, ";"), each Number.From(_)))), each Text.From(List.Sum(_))), ";")},
        {"new2", each Text.Combine(List.Transform(List.Zip(List.Transform([ColumnC], each List.Transform(Text.Split(_, ";"), each Number.From(_)))), each Text.From(List.Sum(_))), ";")},
        {"new3", each Text.Combine(List.Transform(List.Zip(List.Transform([ColumnD], each List.Transform(Text.Split(_, ";"), each Number.From(_)))), each Text.From(List.Sum(_))), ";")}
    })
in
    Data


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 2 guests