Create a Table with Unique IDs and associated Concatenated Values column

[jase71ds]

Table 1 has ID column (not unique) with FileName column
I need to create a table that makes ID column unique, and concatenates all the associated FileNames
Probably easier to show, than explain.
Please see image.

I'm stumped. No idea how to go about doing this. Any help is appreciated!
Jase.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Norie]

If the data for Table1 is in A1:B8 you could use these formulas.

In a single cell, let's say E1.

=UNIQUE(A1:A8)

Now in F1 and copied down.

=TEXTJOIN(",",FALSE, FILTER($B$1:$B$8, $A$1:$A$8=E1))

 

[jase71ds]

If the data for Table1 is in A1:B8 you could use these formulas.

In a single cell, let's say E1.

=UNIQUE(A1:A8)

Now in F1 and copied down.

=TEXTJOIN(",",FALSE, FILTER($B$1:$B$8, $A$1:$A$8=E1))

But that is Excel, right?
I'm trying to do this in Power Query. I just did a screenshot in Excel to make the point about the table structures.
Alternately, I guess I could use DAX in PBI. I'll see if DAX has the equivalent Excel UNIQUE/FILTER/TEXTJOIN

 

[Norie]

Apologies, didn't notice this was in the Power BI forum.

Here's M-code that will do the job after you've imported Table1.

let
    Source = Table1[ID],
    #"Reversed List" = List.Reverse(Source),
    #"Removed Duplicates" = List.Distinct(#"Reversed List"),
    #"Converted to Table" = Table.FromList(#"Removed Duplicates", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Merged Queries" = Table.NestedJoin(#"Converted to Table", {"Column1"}, Table1, {"ID"}, "Table1", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each Table.AddColumn(#"Merged Queries", "Test", each Table.ToList([Filename]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Table.Column([Table1], "FileName")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"Table1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns1",{{"Column1", Order.Ascending}})
in
    #"Sorted Rows"

Note, that's a bit messy - a few extraneous steps here and there.

 

[jase71ds]

I will try this in the morning and let you know.
Thanks so much!!