Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Concatenate conditionally once and leave blank for the duplicate occurrence

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.
WillTaylorUK_0-1670716042066.png

 

Expected Outcome

WillTaylorUK_1-1670716098123.png

 

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"

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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]),

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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

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/

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors