Group By/Row Aggregation & Concatenation / Text.Combine while ignoring duplicates, nulls and blanks

[SHammer]

Note: For simplicity and due to original data being sensitive, I reduced my actual aggregation down to the "Before:" and "After:" examples / tables listed at the end of my post.  The "After:" is what I would like the result to be.

I want to condense an item (Fruit) to a single, row level and for an alternate, corresponding column (Fruit Type), concatenate only the unique values using a comma delimiter while ignoring duplicates, nulls and blanks.

As a second related request, I would also like to know how to complete these actions if the concatenate column (Fruit Type) had date values instead of text values (Fruit Ship Date).

Here is my original Group By / Row Aggregation:
#"Grouped Rows" = Table.Group(Source, {"Fruit"}, {{"Fruit Type", each List.Sum([Fruit Type]), type text}})

M Code Revision: Integrated concatenation / Text.Combine, ignore duplicates, ignore nulls and using a comma delimiter:
#"Grouped Rows" = Table.Group(Source, {"Fruit"}, {{"Fruit Type", each Text.Combine(List.Distinct(List.RemoveNulls([Fruit Type])), ","), type text}})

Ignoring blanks is where I am stuck, I believe. I am still getting results like: "Gala,Fuji," or ",Gala,Fuji" or "Navel,Valencia,,". I would like it to appear as "Gala,Fuji" or "Fuji,Gala" without any additional, unnecessary commas.  

Before:

Fruit	Fruit Type
Apple	Gala
Orange	Navel
Orange	Valencia
Apple	Fuji
Apple
Orange
Orange
Grapefruit	Red
Orange	Navel
Grapefruit	Pink

After:

Fruit	Fruit Type
Apple	Gala,Fuji
Orange	Navel,Valencia
Grapefruit	Pink,Red

[mahoneypat]

Can you clarify what you need for dates?  Before and after examples again would be useful.

Also, I'm curious why you are making string lists instead of leaving them in separate rows, which may limit your analysis options.  You can always generate a string list in a measure with CONCATENATEX as needed, but have the data in separate rows for all your other analyses.

Regards,

Pat

[SHammer]

I would love to share, but can't for company privacy reasons.  The data I am provided is awful and at this time I cannot get them to clean it, summarize it, ensure it's complete, etc., beforehand and I do not have IT resources to do so on my end. 

I am tworking with shipment data which has multiple row entries (and column entries) per shipment, often up to 10+ rows for various reasons such as different Master Bill of Lading's (MBOL's), Customs Ref#'s, PO#, etc.  Other users want the per shipment data in a table which makes it much more complicated for me as I cannot just fall back to the Data Model and visuals.  To get to a shipment level cost, but still display the various cost categories, milestone dates, ship from/to, etc. and I need details such as the string list of MBOL's.  I have experienced missing cost and found it was due to missing MBOL's for the shipment, so need this concantenation for further validations when something appears awry with the cost.  I also need to create data validation processes and when issues are found, complete interim manual overides and submit the issue(s) details to the data source.  This is where the dates come in.  I have found many issues with multiple dates for delivery, etc. (shipment milestones) when it is physically impossible.

I definitely am a newbie to Power Query as a whole so started off mostly with the UI and learning M, now need to start integrating DAX.  When you know better, you do better.  Will need to look into your suggestion to see how/if I can apply it to this scenario.  Thanks so much for your further inquiry and suggestions!

[mahoneypat]

Looks like you have spaces in there too (or blanks).  You can use List.Select to get rid of those

= Text.Combine(List.Select(List.Distinct(List.RemoveNulls([Fruit Type])), each _ <> " "),",")

Regards,

Pat

[SHammer]

Success!!  Thank you so much @mahoneypat!  Can you tell me how I would complete this same practice for dates?  Assuming it won't be Text.Combine or will need an additional syntax(es).

As info, I tried to integrate what you provided prior to posting, I obviously missed a comma, paranthesis or just didn't enter it in the correct order. 

Four mo.'s ago I began using PQ / Power BI, M / DAX, teaching myself as time allows via websites and videos.  My source data is awful.  Attempting to get skilled quickly while making my queries efficient.

Thanks again for your assistance!