Lookup from multiple values in same column

To build on Greg's post, if you first need to generate the table with the combinations of Units, you can do that in the query editor.  Here is one way to do it, using your sample data.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUapQitWJVjKCs4yBrEo4CyFbBWYZwmWNUNQBZWMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Unit = _t]),
basetable = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Unit", type text}}),
Units = List.Distinct(basetable[Unit]),
#"Converted to Table" = Table.FromList(Units, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "AddUnitsList", each Units),
#"Expanded AddUnitsList" = Table.ExpandListColumn(#"Added Custom", "AddUnitsList"),
#"Filtered Rows" = Table.SelectRows(#"Expanded AddUnitsList", each ([Column1] <> [AddUnitsList])),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "SortedList", each List.Sort({[Column1], [AddUnitsList]})),
#"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"SortedList"}),
#"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"SortedList"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Unit1"}, {"AddUnitsList", "Unit2"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Unit1", type text}, {"Unit2", type text}})
in
#"Changed Type1"

It results in this table, with which you can then do a concatenatex expression.

Let me know if you're interested to take this query further to also generate the final table (an M approach vs. DAX).  I started doing a CROSSJOIN() in DAX to make the table above.  It is doable, but doing it in the query was easier (at least for me).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat