Forum Discussion
Netrelemo
Helper IV
4 years agoHow do I sort a concatenated list?
This solution from a previous post (Solved: Re: Ungroup a date grouping - Microsoft Power BI Community) works perfectly ... except that I want the Activities field sorted alphabetically.
I've tried inserting various List.Sorts but I can't get it right.
Could have sworn there was an answer to it this morning, but no longer. 😕
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDLDsIgEEX/hXUThoEqXfrY+A1NFy5IQ9JSI9TvlwbEobrjhnPuwPQ9O52laFnDLtPi16eJJwkcDhwBkQbFhuZL3+bHZGbjwj3YxW2c4AKKJDgctyBryb2MD3YkDugNE3mQSgXZkbtnfSZAprukUvpqvB0dvYc8R1JYKb2vTgDSICu6VGPHAWl1m8JfmPwqmjqbdfPPLguHdELcy/AG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectID = _t, PhaseName = _t, FinPeriodStart = _t, FinPeriodFinish = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectID", type text}, {"PhaseName", type text}, {"FinPeriodStart", type date}, {"FinPeriodFinish", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"FinPeriodStart"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"ProjectID"}, {{"ProjectID.1", each _, type table [ProjectID=nullable text, PhaseName=nullable text, FinPeriodFinish=nullable date]}, {"FinPeriodFinish", each List.Max([FinPeriodFinish]), type nullable date}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Activities", each Table.Column([ProjectID.1], "PhaseName")),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"ProjectID.1"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Activities", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
#"Extracted Values"
Netrelemo , change the last step and check
#"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Activities", each Text.Combine(List.Sort(List.Transform(_, Text.From)), ","), type text})
1 Reply
- amitchandak
Super User
Netrelemo , change the last step and check
#"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Activities", each Text.Combine(List.Sort(List.Transform(_, Text.From)), ","), type text})