Power Query Sum columns w/ duplicate values

Power Query
1

Hello EDNA - needed a hand or two with this challenge.

Big Picture, I’m looking to create a look up table using the PBI Summarize function. Challenge , the table I’m looking to use has some duplicate values in the [WO] column which I will use as my KEY.

I need to sum numerical columns and the the text columns keep date and separate by comma. like the pic below.

image
image1257×406 26.1 KB

Any help would be appreciated,
Tracker.pbix (111.5 KB)

Frankee

2

Hi @Frankee ,
Thank you very much for post your query into then forum.

Check out this post to see if it can help you.

Using Group by to Concatenate Text in Power Query - BI Gorilla

image
image702×465 6.09 KB

For text columns you need to concatenate, change this which gives an error:

{“SO”, each List.Sum([SO]), type nullable text}

To:

{“SO”, each Text.Combine([SO], ", "), type nullable text}

image
image840×575 15.1 KB

Regards,

3

Hey @jafernandezpuga , Looks like this will do it. Thank you for the help. Have not replied because im trying to implement to several colums … so far so good.

Thanks again buddy,

Frankee.

1 Like
4

Hey @jafernandezpuga - I did find one place are im currently stuck at. I do have a data column, which would also need to be combined / concatenated.
this currently will not work:
{“Vendor Date”, each Text.Combine([Vendor Date],", "), type nullable date},

need this to show once combined as 10/10/2022 , 10/15/2022…something close to this.

Thank you for your help in advance,

Frankee.

5

Hi @Frankee,
Try the following query where we convert the values of the list of dates to text and then combine them.

let
    Source = Excel.Workbook(File.Contents("C:\Enterprise DNA\Forum\Power Query Sum columns w duplicate values\Tracker.xlsx"), null, true),
    Hoja1_Sheet = Source{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SO", type text}, {"Desc", type text}, {"Vendor", type text}, {"Vendor2", type text}, {"WO", type text}, {"Ref", type text}, {"TS Pend", Int64.Type}, {"SE Pend", Int64.Type}, {"-", type any}, {"Paid", Int64.Type}, {"_", type text}, {"Limit", Int64.Type}, {"% Paid", Int64.Type}, {"Remaining", Int64.Type}, {"V Date", type date}, {"Status", type text}, {"By", type text}, {"VS", type text}, {"22-Jan", type number}, {"22-Feb", type number}, {"22-Mar", type number}, {"22-Apr", type number}, {"22-May", type number}, {"22-Jun", type number}, {"22-Jul", type number}, {"22-Aug", type number}, {"22-Sep", type number}, {"22-Oct", type number}, {"Total", type number}, {"Money left", type number}, {"Percentage left", type number}, {"Column1", type number}, {"Description2", type text}, {"Craft", type text}, {"FC", Int64.Type}, {"Notes", type text}, {"Uploaded ", type text}, {"Est Complete", type text}, {"Tracker Complete", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WO"}, { {"TS Pend", each List.Sum([TS Pend]), type nullable number},
     {"SO", each Text.Combine([SO], ", "), type nullable text},
    {"Vendor Date", each Text.Combine( List.Transform( [V Date], each Date.ToText( _ , "dd/MM/yyyy" ) ), ", "), type nullable text} } )
in
    #"Grouped Rows"

Regards,

6

@jafernandezpuga …got it bud.

{“Vendor Date”, each Text.Combine( List.Transform( List.Distinct([Vendor Date]), each Date.ToText( _ , “MM/dd/yyyy” ) ), ", "), type nullable text},

Thank you much. I saw that the last two had duplicate dates and added the “List.Distinct”…for a single value.

image

Thanks again,

Frankee

1 Like