Combine all unique values within a group | Excel Power Query 04

Let’s assume that we are in the future and we sell space­ships. We want to find out what class of a space­ship each coun­try has bought bas­ing on our table of unique space­ship classes. 

With Pow­er Query, this task is very sim­ple. We have to select our main table, then go to the Data tab and choose the From Table/Range com­mand in order to export our data from Excel to Pow­er Query (Fig. 1).

Since we are inter­est­ed which coun­try has bought which space­ship, we want to group our data by the coun­try. We have to select the Coun­try col­umn and go to the Home tab (1) and choose the Group By com­mand (2). In the win­dow, we need to choose the Advanced option (3) because we want to add more aggre­ga­tion. I want to count how many ships each coun­try has bought as well as know the space­ship class names. Since there isn’t any prop­er oper­a­tion, let’s choose the Sum option (4), which we will mod­i­fy lat­er in the code. In the col­umn bar, we’re choos­ing the Ship class (5). As a final step, let’s write a new name, which is going to be ‘Unique ship class­es’ (6) (Fig. 2)

In the new table, there are some errors in the last col­umn, because we want­ed to sum up text, which is impos­si­ble. It means that we need to mod­i­fy our code. We need to go to the View (1) tab and tick the For­mu­la bar check­box (2). We can see that the for­mu­la uses the List.Sum func­tion (3) (Fig. 3)

What we want to do is com­bine all val­ues, so let’s change the List.Sum into the Text.Combine func­tion (1). We also need to add a sep­a­ra­tor, which will be a com­ma and a space (2) (Fig. 4)

Now, we can see that Pow­er Query has changed its cal­cu­la­tions. All ship class­es are com­bined. How­ev­er, as I said before, we need only unique val­ues with­out repeats. We just need to add one func­tion, which is the List.Distinct func­tion. (Fig. 5)

Now, we can see there are no repeats. We can go to the Home tab (1) and to the Close & Load com­mand (2) and the Close & Load to option (3) (Fig. 6)

In Excel, we have to remem­ber to select the Table (1) and Exist­ing work­sheet (2) radio but­tons. Then choose a tar­get cell (Fig. 7)

As we can see, we have count­ed how many ships each coun­try has bought as well as the prod­uct class (Fig. 8)

https://www.youtube.com/watch?v=A0fWOeDUXNA