Combining Data, Separated by a Comma when Matching value in One Column in Power BI Desktop

[ccolletti]

Hi All,

I'm working on building a rollup of data to match Vendors to the State and Store numbers they work in. I was able to pull this data in Excel using TextJoin, but I want to utilize Power BI for when the dataset is refreshed to account for any new stores or vendors.

Currently in excel I'm using a similar formula to pull together the state and store numbers and I get the following data.

=TEXTJOIN(", ",TRUE,SORT(UNIQUE(IF('Vendor - Store List'!A:A='Vendor - Store Rollup'!A2,'Vendor - Store List'!C:C,""))))

In Power BI my data reads as the following:

I've attempted to use CONCATENATEX, CONCATENATEX(VALUES, and SUMMARIZE  from the following link with no results. The data I get back is every store number seperated by a comma for every vendor.

https://community.powerbi.com/t5/Desktop/excel-Text-Join-method-with-Dax/m-p/569470

Thank you all for your help and time looking into this!

[Padycosmos]

Hope this video helps:

https://www.youtube.com/watch?v=6g49UJ096NE

[Padycosmos]

This video illustrates how to combine rows in Power query

https://drive.google.com/file/d/1na2-YBiK2AGJVbmC85IQiue3E03uVikv/view?usp=share_link

[ccolletti]

@Padycosmos, unfortunelty I'm unable to access the video due to this being a work computer. Is it possible to post screenshots? Thank you!

[Padycosmos]

Here are the screen shots:

[ccolletti]

@sevenhills Thank you for your help, this halfway helped me out.

Using the new measure within the table for the State worked perfectly, however the store is still not working correctly. Not sure what I'm missing as I did the fromula exactly how you wrote it out for both and the States came back fine, but the store still just pulled in every single store number.

[sevenhills]

@ccolletti , the logic is same and not sure where it went wrong. Can you post the DAX?

Simple thumb rule: it should work for both the same way. As you have seen, DAX is simple code. All we are doing is getting distinct and concatenex. 

[ccolletti]

@sevenhills, agreed I figured it would work with State working. And not able to post in DAX, table is locked down by IT. Only have the ability to access the data for design purposes.

[sevenhills]

It is not possible to help without knowing the table, dax and the issue.

Let us do Power Query route:
Ask them to try in Power Query and see if that works there?

Say, you have table as below:

Note: Store is marked as text.

Add a group by step as below:

= Table.Group(#"Changed Type", {"Vendor"}, 
        {
         {"States", each Text.Combine ( List.Distinct([State], Comparer.OrdinalIgnoreCase), ", "), type text}
         , {"Stores", each Text.Combine ( List.Distinct([Store], Comparer.OrdinalIgnoreCase), ", "), type text}
        })

and you can see the output as

Another advantage is Power Query stores, where as Measure calculates in run time. 

Hope this helps!

[sevenhills]

Two approaches, you can decided based on your needs.

1) Create new DAX table

2) Creating measures in the same table

New Table using DAX:

Table52Vendor_Rollup = 
    Summarize(
        Table52Vendor, Table52Vendor[Vendor], 
           "States", CONCATENATEX(DISTINCT(Table52Vendor[State]), Table52Vendor[State], ", "),
           "Stores", CONCATENATEX(DISTINCT(Table52Vendor[Store]), Table52Vendor[Store], ", ")
    )

New Measure within the table

Vendor States = CONCATENATEX(DISTINCT(Table52Vendor[State]), [State], ", ")

Vendor Stores = CONCATENATEX(DISTINCT(Table52Vendor[Store]), [Store], ", ")