Combine all possible values from a single column- Power query

Anonymous Using my code I referenced previously, I turned it into a function fn_Subsets that transforms a list into a list of subsets (a list of lists).

(L as list) as list =>
let
  N = List.Count(L),
  Subsets =
      List.Transform(
          {0..Number.Power(2, N)-1},
          (i) => List.Transform(
                     {0..N-1},
                     (j) => if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1
                            then L{j}
                            else null
                 )
      ),
  RemoveNulls = List.Transform(Subsets, each List.RemoveNulls(_))
in
  RemoveNulls

 

We can apply this function in a Group By set to each set of companies associated with each ID.

 

Here's a complete sample query (including the function definition) you can paste into the Advanced Editor of a new blank query.

let
    /*Define a list function. This is usually done in a separate query.*/
    fn_Subsets = (L as list) as list =>
    let
        N = List.Count(L),
        Subsets =
            List.Transform(
                {0..Number.Power(2, N)-1},
                (i) => List.Transform(
                            {0..N-1},
                            (j) => if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1
                                    then L{j}
                                    else null
                        )
            ),
        RemoveNulls = List.Transform(Subsets, each List.RemoveNulls(_))
    in
        RemoveNulls,

    /*Define sample dataset. Replace with your own data.*/
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rcy7DcAgFEPRXVxTBPIvSQjkMwJ6+68RIyG5uJJP41oRPRwi8zDXebAgnmwVE9vEi+1ibleDXJr7d+C+2Sg+bBJfNosfW2D2Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Company = _t, Price = _t]),
    SampleData = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Company", type text}, {"Price", Int64.Type}}),
    
    /*Logic applying the subsets function and aggregating the results.*/
    #"Grouped Rows" = Table.Group(SampleData, {"ID"}, {{"SubsetList", each fn_Subsets([Company]), type list}}),
    #"Expanded Count" = Table.ExpandListColumn(#"Grouped Rows", "SubsetList"),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Company", each Text.Combine([SubsetList], ","), type text),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Company] <> "")),
    #"Expanded SubsetList" = Table.ExpandListColumn(#"Filtered Rows", "SubsetList"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded SubsetList", {"ID", "SubsetList"}, SampleData, {"ID", "Company"}, "Expanded SubsetList", JoinKind.LeftOuter),
    #"Expanded Expanded SubsetList" = Table.ExpandTableColumn(#"Merged Queries", "Expanded SubsetList", {"Price"}, {"Price"}),
    #"Aggregate Rows" = Table.Group(#"Expanded Expanded SubsetList", {"ID", "Company"}, {{"Sum_Price", each List.Sum([Price]), type nullable number}, {"No of Supplier", each Table.RowCount(_), Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Aggregate Rows",{{"ID", Order.Ascending}, {"No of Supplier", Order.Ascending}, {"Company", Order.Ascending}})
in
    #"Sorted Rows"