Data manipulation/translation/power query help

[Joshua_P]

Hello, I have a list of pricing/sales for customers. The way it's sorted I'm pulling off the the SKU and I want to pull in a dataset that has the SKU and the customer names but concatonated instead of separated.

The closest I've been is to just put it in a povit and individually used a =textjoin but that's not sustainable. Here's an example of the dataset and result I'm trying to get from data formatted like this but with a lot more SKUs.

I would like pbi to do this for me 

SKU	Customer
1234	Facebook
1234	Amazon
1234	Apple
1234	Netflix
1234	Google
Output
1234	Facebook, Amazon, Apple, Netflix, Google

[Anonymous]

Hi  @Joshua_P ,

Here are the steps you can follow：

1. Create calculcated table.

Table 2 =
SUMMARIZE(
    'Table',
    'Table'[SKU],
    "Customer",CONCATENATEX(FILTER('Table','Table'[SKU]='Table'[SKU]),'Table'[Customer],","))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Show a more relevant example with more SKU's and Customers and on that larger dataset, show the expected result.

[PhilipTreacy]

Hi @Joshua_P 

This code produces the result you specified - download the sample PBIX file

But by the sounds of your latest message your data isn't quite arranged as laid out in your initial post.

If this code isn't workign for you please post a real example of your data structure, the actual data can be modified/anonymized, but the column names and order are important.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[amitchandak]

@Joshua_P , Try concatenatex

concatenatex(Table,table[Customer])

Also refer: https://www.youtube.com/watch?v=du2HSEzng2E

[Joshua_P]

Thank you for taking the time to respond. Unfortunatley this doesn't seem to be working, I've tried testing a few variations. Maybe I'm doing it wrong.

I have multiple SKUs and multiple company names to go with them and when I use this formula it's mostly just taking all of the customer names and not just the ones that share a SKU.

[OwenAuger]

Hi @Joshua_P 

You can Table.Group with Text.Combine to group by SKU and concatenate the Customer values.

Here is a sample query, with the "Grouped Rows" step being the key step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUXJLTE5Nys/PVorVgYs55iZW5eehiBQU5KQiC/illqTlZFYgC7nn56eDFMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {{"Customers", each Text.Combine([Customer],", "), type text}})
in
    #"Grouped Rows"

Regards,

Owen

[Joshua_P]

Hello! Thank you so much for putting this together. Unfortunately I can't seem to get it to work.

I'm pretty new with the coding pat of it like this so I tried to replace the correct variables and it sell doesn't seem to be working.

The SKU, and the Customer Name are on the same page along with some other data separated by SKU with the SKU being the really only duplicated value in the sheet other than the company names as well.