Concatenated list of years per uniqueID

[cgreaves]

In my data, some entries have unique IDs that are associated with future (and past) fiscal years. 

I'm trying to get a list of dates associated with each uniqe ID. 

I also need to filter out all years prior to 2024. 

Example Data: 

UniqueID	Fiscal Year	(desired outcome)
001	2026	2024, 2026
001	2024	2024, 2026
002	2025	2025
003	2028	2025, 2028
003	2025	2025, 2028
003	2019	2025, 2028

I'll be using this coumn in a table in a report (filtered by fiscal year) so I think the best solution for me would be a new column in power query editor. 

I'm very new to power BI and I've been racking my brain (and this forum) trying to figure out how to do this, but nothing I've come across so far really helps my problem. 

Any and all suggestions are welcome.

Thanks 

[ronrsnfld]

In Power Query, you can do this with the Table.Group function and a custom aggregation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRMjIwMlOK1UHimkC5RhCuKZRrDOFaoHJRZQ0tlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, #"Fiscal Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"Fiscal Year", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"UniqueID"}, {
        {"all", each _, type table [UniqueID=nullable number, Fiscal Year=nullable number]},
        {"Fiscal Years", (t)=> Text.Combine(
            List.Transform(
                List.Select(t[Fiscal Year], each _ > 2019),
                each Text.From(_)),
            ", "), type text}
        }),

    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Fiscal Year"}, {"Fiscal Year"})
in
    #"Expanded all"

Original Data

Results

[ppm1 · Accepted solution]

While it is possible to do that in the query editor, it is better/easier to do it as a DAX measure. Replace T1 with your actual table name.

Concat FY =
VAR vYears =
    DISTINCT (
        CALCULATETABLE (
            DISTINCT ( T1[Fiscal Year] ),
            ALL ( T1 ),
            VALUES ( T1[UniqueID] )
        )
    )
RETURN
    CONCATENATEX (
        FILTER ( vYears, T1[Fiscal Year] >= 2024 ),
        T1[Fiscal Year],
        ", "
    )

Pat

[cgreaves]

Wow this worked perfectly! Thank you so much!

I just had to add double quotes to "2024" because the dax didn't like comparing values to text. Also added

,T1[Fiscal Year], ASC)

to the concatenatex function to sort in ascending order.