Concatenate conditionally once and leave blank for the duplicate occurrence

[Anonymous]

This can probably be done easily but I can't figure it out.

The results should be a semi colon seperated column Result with state code from column H when Name matches Column F.

Result Column I will need to have a blank value or other value if it has already been matched or value picked up in the first Name row.

So, this is my Input Data.

Expected Outcome

I have tried using the following M-Code, however I think there is an easy way or short way to do this. 

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each _, type table [Name=text, States=text, State Code=text]}, {"Sum", each Text.Combine([State Code],"; "), type text}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"States", "State Code", "Index"}, {"States", "State Code", "Index"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Result", each if [Index] = 1 then [Sum] else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"All", "Sum", "Index"})
in
#"Removed Columns"

[Anonymous]

Hi @tackytechtom yes it worked very well, if I can ask, what is this line 

Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDIbfpee9RDcPG4p4duzwSwcL1BSiQ93T2xYmlBRP/dsv+ZqMo2mFwAzTmIFdjDmdzdQUyHviQI8UT5kd8fEUr9bjhnuqsSVQth9QLuvnN9gJar4CCm0hO+1VwcFhCek8KNTjBUp/94Nu+zuO2qJbsFW7dlBOccEaC6uLdZDnLFVXKysHLdufi3WmLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, States = _t, #"State Code" = _t]),

[tackytechtom]

Hi @Anonymous ,

That line is created by Power Query when you manually enter data.

Great that it helped!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCsIwDIbfpee9RDcPG4p4duzwSwcL1BSiQ93T2xYmlBRP/dsv+ZqMo2mFwAzTmIFdjDmdzdQUyHviQI8UT5kd8fEUr9bjhnuqsSVQth9QLuvnN9gJar4CCm0hO+1VwcFhCek8KNTjBUp/94Nu+zuO2qJbsFW7dlBOccEaC6uLdZDnLFVXKysHLdufi3WmLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, States = _t, #"State Code" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each _, type table [Name=text, States=text, State Code=text]}, {"Result", each Text.Combine([State Code],"; "), type text}}),
#"Expanded All" = Table.ExpandTableColumn( Table.TransformColumns(#"Grouped Rows",{ "All", each Table.FirstN(_,1)}) , "All", {"States"}, {"All.States"}),
#"Merged Queries" = Table.NestedJoin(Source, {"Name", "States"}, #"Expanded All", {"Name", "All.States"}, "Expanded All", JoinKind.LeftOuter),
#"Expanded Expanded All" = Table.ExpandTableColumn(#"Merged Queries", "Expanded All", {"Result"}, {"Result"})
in
#"Expanded Expanded All"

Let me know if this one works foru you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/