Forum Discussion

Netrelemo's avatar
Netrelemo
Icon for Helper IV rankHelper IV
4 years ago
Solved

How 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

  • 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})