Forum Discussion

ccolletti's avatar
ccolletti
Helper I
3 years ago

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

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...
Need Help
ccolletti's avatar
ccolletti
Helper I
3 years ago

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's avatar
sevenhills
Super User
3 years ago

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!