Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Something like:
MyColumn = Source[Customer Name],
Sorted = List.Sort(MyColumn),
AsText = List.Transform(Sorted, Text.From),
CommaSeperated = Text.Join(AsText, ",")
Something like:
MyColumn = Source[Customer Name],
Sorted = List.Sort(MyColumn),
AsText = List.Transform(Sorted, Text.From),
CommaSeperated = Text.Join(AsText, ",")
Yep, absolutely! That's way better, thank you! 😁
(One small edit for anyone reading this, the final function should be Text.Combine.)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.