cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pelowski Frequent Visitor
Frequent Visitor

More Efficient Steps to Achieve Comma Delimited List of IDs

I feel like there are probably functions I should be using to make the following steps more efficient.

 

All I need to do is take data in a table, sort a given value numerically (which makes it easier to debug/review when necessary) and then return a sorted, comma-separated text value that I can then pass along to a SQL query "IN" statement.  Since I do this fairly often I'd like to trim off as many steps as I can if possible.

let
	Source = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
	#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Customer ID", Int64.Type}}),

	// Does it make sense to sort it as a table numeric value then change it to type before doing a Table.ToList?
	// Table.ToList fails on a numeric data type
	#"Removed Other Columns" = Table.Sort(Table.SelectColumns(#"Changed Type",{"Customer ID"}), {"Customer ID", Order.Ascending}),
	#"Transform Type to Text and Make List" = Table.ToList(Table.TransformColumnTypes(#"Removed Other Columns",{{"Customer ID", type text}})),

	// Are there better ways to add the comma delimiter without having to trim it at the end?
	#"List of IDs with Trailing comma" = Lines.ToText(#"Transform Type to Text and Make List", ","),
	#"Comma Separated String of Customer Ids" = Text.Start(#"List of IDs with Trailing comma", Text.Length(#"List of IDs with Trailing comma") - 1)
in
	#"Comma Separated String of Customer Ids"

 

Any help you can provide to make this more streamlined would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
artemus Member
Member

Re: More Efficient Steps to Achieve Comma Delimited List of IDs

Something like:

 

MyColumn = Source[Customer Name],
Sorted = List.Sort(MyColumn),
AsText = List.Transform(Sorted, Text.From),
CommaSeperated = Text.Join(AsText, ",")

 

View solution in original post

2 REPLIES 2
artemus Member
Member

Re: More Efficient Steps to Achieve Comma Delimited List of IDs

Something like:

 

MyColumn = Source[Customer Name],
Sorted = List.Sort(MyColumn),
AsText = List.Transform(Sorted, Text.From),
CommaSeperated = Text.Join(AsText, ",")

 

View solution in original post

pelowski Frequent Visitor
Frequent Visitor

Re: More Efficient Steps to Achieve Comma Delimited List of IDs

Yep, absolutely!  That's way better, thank you!  😁

(One small edit for anyone reading this, the final function should be Text.Combine.)

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,047)