How to Pivot and combine List of Items per Segment using M-Query in Power BI

How to use Text.Combine to combine List of Items per Segment using Power Query

Scenario:
Suppose we have a sample table with the columns as Product_Name, Product_Segment and Country as per below:

Now based on the above table, we want to Piovt the data by Country and show the combined list of Products per Product Segment separated by a delimiter (|) for each Country.

We can achieve this Scenario using the Text.Combine function as shown below:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBDoIwEER/hfTMT6gR0YSLIfFAOKztBjbWhWzB+PlaY1ICAp6adt50ZrcoVI4WH+SoYRWrvUXdScOknb9xZYGNKuNCpSCG3G2ROTd9hzJBEgHWuPjLH8iRDcFSTAB2NZDXk16Yul5w1HOLZqQO8nO4WpyXp96Q+8sa1KnzBC3wrDOoiZCp/FPa3DHatK0l32e8/gu4mriKMtDvc53P6IkSHYTYfNY5pQdzr8PfvuUL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Prod_Name = _t, Prod_Segment = _t, Country = _t]),

    ChangeType = Table.TransformColumnTypes(Source,{{"Prod_Name", type text}, {"Prod_Segment", type text}, {"Country", type text}}),

    PivotByCountry = Table.Pivot(ChangeType, List.Distinct(ChangeType[Country]), "Country", "Prod_Name", each Text.Combine(_,"|"))

in

    PivotByCountry

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------