Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- Re: Combine all possible values from a single colu...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Combine all possible values from a single column- Power query

07-08-2022
04:23 AM

Raw Data

How can I get all the possible combination of 7 companies with unique ID?

-Count 1-7

-All Combination of company, e.g. count=1, only show A/B/C/D/E/F/G price in one row

-Sum of the combined companies price

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-11-2022
11:41 AM

@simesman 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"
```

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-08-2022
05:36 PM

- First, you must group the table by ID and return all rows and perform the sum of the price.

2. After, add a new custom column and use the function *Combiner.CombineTextByDelimiter*. Use the delimiter "/" and combine the collumn from grouped table *COMPANY*;

`= Combiner.CombineTextByDelimiter("/")([TABLE_GROUP][COMPANY])`

Finally, only select the columns

**Sample M**

```
let
Fonte = Excel.CurrentWorkbook(){[Name="sAMPLE"]}[Content],
GroupID = Table.Group(Fonte, {"ID"}, {{"TABLE_GROUP", each _, type table [ID=nullable text, COMPANY=nullable text, PRICE=nullable number]}, {"SUM_PRICE", each List.Sum([PRICE]), type nullable number}}),
CombineCompany = Table.AddColumn(GroupID, "COMPANY", each Combiner.CombineTextByDelimiter("/")([TABLE_GROUP][COMPANY]), type text),
SelectColumns = Table.SelectColumns(CombineCompany,{"ID", "COMPANY", "SUM_PRICE"})
in
SelectColumns
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-09-2022
07:13 AM

Thank you for your comment. But I would like to know how many possible combination and its sum of price for 1to 7 suppliers.

e.g

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-12-2022
03:36 AM

Hi @simesman ,

With the brilliant code provided by @AlexisOlson, I applied it to your case. This is not achievable without Alexis' code. Very smart!!

Please see below outcome:

Open a blank query and paste over the code below (delete out the existing code first).

Code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyBGJjY6VYHSjfCYiNjBB8ZyA2gcobwdSbIPhg9UjyIPWGQP2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Company = _t, Price = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Company", type text}, {"Price", Int64.Type}}),

//Group rows to do the count of companies

#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

//Combination transformation - Company

Subset = Table.AddColumn(#"Grouped Rows", "Subset", each List.Transform(

{0..Number.Power(2, [Count])-1},

(i) => List.Transform(

{0..[Count]-1},

(j) => if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1

then Table.SelectRows(#"Changed Type", (x)=>x[ID]=[ID])[Company]{j}

else null

)

)),

Concatenate = Table.AddColumn(Subset, "Concatenate", each List.Transform([Subset], each Text.Combine(List.RemoveNulls(_), ","))),

//Combination transformation - Price

#"Subset$" = Table.AddColumn(Concatenate, "Subset $", each List.Transform(

{0..Number.Power(2, [Count])-1},

(i) => List.Transform(

{0..[Count]-1},

(j) => if Number.Mod(Number.IntegerDivide(i, Number.Power(2, j)), 2) = 1

then Table.SelectRows(#"Changed Type", (x)=>x[ID]=[ID])[Price]{j}

else null

)

)),

#"Concatenate$" = Table.AddColumn(#"Subset$", "Concatenate$", each List.Transform([#"Subset $"], each List.Sum(List.RemoveNulls(_)))),

//Join list (Company & Price)

#"Added Custom" = Table.AddColumn(#"Concatenate$", "Company", each List.Zip({[Concatenate],[#"Concatenate$"]})),

//Expand join list

#"Expanded Combine" = Table.ExpandListColumn(#"Added Custom", "Company"),

//Expand join sub-list

#"Expanded Combine1" = Table.ExpandListColumn(#"Expanded Combine", "Company"),

#"Added Index" = Table.AddIndexColumn(#"Expanded Combine1", "Index", 0, 1, Int64.Type),

#"Added Custom1" = Table.AddColumn(#"Added Index", "Price", each #"Added Index"[Company]{[Index]+1}),

//Aligning Company and Price

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Filter", each try Value.Is([Price], type number) otherwise false),

//Filter out unnecessary rows

#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Filter] = true)),

//Remove unnecessary columns

#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"ID", "Company", "Price" }),

//number of supplier

#"Added Custom3" = Table.AddColumn(#"Removed Other Columns", "No of Supplier", each List.Count(Text.Split([Company],",")))

in

#"Added Custom3"

Regards

KT

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-08-2022
04:09 PM

Hi @simesman ,

Please see code below (highlighted code show is the code to get the combination):

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyBGJjY6VYHSjfCYiNjBB8ZyA2gcobwdSbIPhg9UjyIPWGQP2xAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Company = _t, Price = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Company", type text}, {"Price", Int64.Type}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each Table.SelectRows(#"Changed Type", (x)=>x[Company]=[Company])[ID]),

#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Count", each Text.Combine(List.Transform(_, Text.From), ";"), type text})

in

#"Extracted Values"

Regards

KT

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-09-2022
07:13 AM

Thank you for your comment. But I would like to know how many possible combination and its sum of price for 1to 7 suppliers within the same ID

e.g

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-10-2022
04:56 PM

You might be interested in my comment here. It has all the basic math needed.

Note that there are 2^7 = 128 combinations for 7 companies. The size can get out of hand really quickly if you try to precompute all possibilities. I'm not sure what your ultimate goal is, but it might be better *not* to try to precompute everything but rather calculate combinations with DAX measures.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-11-2022
11:41 AM

@simesman 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"
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-09-2022
07:11 AM

Raw Data

Result Example

How to get the 7! (factorial) number of all possible combination on company col for the unique ID?

I would like to have 1 to 7 of suppliers and see the all the possible combinations.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-11-2022
11:42 AM

7! is the number of permutations of seven companies, not the number of possible combinations.

See my answer to your previous question here:

https://community.powerbi.com/t5/Power-Query/Combine-all-possible-values-from-a-single-column-Power-...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-09-2022
11:21 AM

Hey!

Isn't this the same question as in Combine all possible values from a single column- ... - Microsoft Power BI Community ?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-10-2022
01:44 AM

Hi @miguel ,

As I havent got the corrected answer , I had created one new question for more elaboration!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-10-2022
04:53 PM

Hi @simesman ,

I'll have a think through about what can be done. It is easy to find out how many combinations but to list them out in a way like A; A B; A C ....... It is quite a challenge, but I will have a go.

Regards

KT