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

Question

Hi All,&nbsp;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.&nbsp;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,""))))&nbsp;In Power BI my data reads as the following:I've attempted to use&nbsp;CONCATENATEX,&nbsp;CONCATENATEX(VALUES, and SUMMARIZE&nbsp; 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&nbsp;Thank you all for your help and time looking into this!&nbsp;

sevenhills · Answer

Two approaches, you can decided based on your needs.
&nbsp;
1) Create new DAX table
2) Creating measures in the same table
&nbsp;

New Table using DAX:
Table52Vendor_Rollup = 
    Summarize(
        Table52Vendor, Table52Vendor[Vendor], 
           "States", CONCATENATEX(DISTINCT(Table52Vendor[State]), Table52Vendor[State], ", "),
           "Stores", CONCATENATEX(DISTINCT(Table52Vendor[Store]), Table52Vendor[Store], ", ")
    )
&nbsp;
New Measure within the table
Vendor States = CONCATENATEX(DISTINCT(Table52Vendor[State]), [State], ", ")

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

padycosmos · Answer

Hope this video helps:https://www.youtube.com/watch?v=6g49UJ096NE

ccolletti · Answer

sevenhills&nbsp;Thank you for your help, this halfway helped me out.&nbsp;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.&nbsp;

padycosmos · Answer

This video illustrates how to combine rows in Power queryhttps://drive.google.com/file/d/1na2-YBiK2AGJVbmC85IQiue3E03uVikv/view?usp=share_link

sevenhills · Answer

ccolletti&nbsp;, the logic is same and not sure where it went wrong. Can you post the DAX?
&nbsp;
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.&nbsp;

Forum Discussion

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

9 Replies

Related content

Tables combine

how to combined skill with separe with (comma)

Thousand-Separator-and-Decimal-Separator

Power BI dashboard from two NPS tables (combine or separate?)

Architecture decision : Separate workspaces