Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dguibert
Frequent Visitor

How to copy values and concatenate based on cell values?

Hi,

 

I have Projects which are deployed with assets and I need to record for each project which assets have been sent to site. Sometimes the Project and Asset are repeated. I need to concatenate the assets and add to that list as long as the project is the same and the asset is new - the 3rd column below is what I am trying to achieve.

 

I can only do this calcualtion in the table view, not in the Query editor since this is a new table created to obain the first 2 columns of projects and assets.

 

Tried lots of things and going nowere. Any help would be greatly apprreciated.

 

dguibert_1-1751916169526.png

 

 

2 ACCEPTED SOLUTIONS
burakkaragoz
Community Champion
Community Champion

Hi @dguibert ,

 

You can achieve this using Power BI's DAX with a calculated column and a bit of row context logic. Since you're working in the table view and not in Power Query, here's a way to do it:

Try adding a calculated column like this:

Result Desired = 
VAR CurrentProject = [PROJECT]
VAR CurrentAsset = [ASSET]
RETURN
CALCULATE(
    CONCATENATEX(
        FILTER(
            'YourTable',
            'YourTable'[PROJECT] = CurrentProject &&
            'YourTable'[Index] <= EARLIER('YourTable'[Index])
        ),
        'YourTable'[ASSET],
        ", "
    )
)

Important: You’ll need an Index column to preserve row order. You can add it in Power BI using "Add Index Column" in Power Query before loading the data.

This will concatenate the assets for each project up to the current row, giving you the cumulative list you're looking for.

Let me know if you need help adapting this to your exact table name or structure.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

This response was assisted by AI for translation and formatting purposes.

View solution in original post

jgeddes
Super User
Super User

If you cannot access the query editor at all, either to do this in M or to add an index, you can try something like this as a calculated column...

Result = 
var _project = 
[PROJECT]
var _vTable = 
SUMMARIZE(
    assetTable,
    assetTable[PROJECT],
    "__value", CONCATENATEX(DISTINCT(assetTable[ASSET]), [ASSET], ", ")
)
RETURN
MINX(
    FILTER(_vTable, [PROJECT] = _project),
    [__value]
)

 It should look like this...

jgeddes_0-1751921023102.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
jgeddes
Super User
Super User

If you cannot access the query editor at all, either to do this in M or to add an index, you can try something like this as a calculated column...

Result = 
var _project = 
[PROJECT]
var _vTable = 
SUMMARIZE(
    assetTable,
    assetTable[PROJECT],
    "__value", CONCATENATEX(DISTINCT(assetTable[ASSET]), [ASSET], ", ")
)
RETURN
MINX(
    FILTER(_vTable, [PROJECT] = _project),
    [__value]
)

 It should look like this...

jgeddes_0-1751921023102.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Whoa! Solved it right away after 3 hours of trying to find out on my own. You saved my day, Thanks!

lbendlin
Super User
Super User

Power BI aggregates by default, so your desired result is not achievable unless you add an index column that makes each data row unique.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

burakkaragoz
Community Champion
Community Champion

Hi @dguibert ,

 

You can achieve this using Power BI's DAX with a calculated column and a bit of row context logic. Since you're working in the table view and not in Power Query, here's a way to do it:

Try adding a calculated column like this:

Result Desired = 
VAR CurrentProject = [PROJECT]
VAR CurrentAsset = [ASSET]
RETURN
CALCULATE(
    CONCATENATEX(
        FILTER(
            'YourTable',
            'YourTable'[PROJECT] = CurrentProject &&
            'YourTable'[Index] <= EARLIER('YourTable'[Index])
        ),
        'YourTable'[ASSET],
        ", "
    )
)

Important: You’ll need an Index column to preserve row order. You can add it in Power BI using "Add Index Column" in Power Query before loading the data.

This will concatenate the assets for each project up to the current row, giving you the cumulative list you're looking for.

Let me know if you need help adapting this to your exact table name or structure.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

This response was assisted by AI for translation and formatting purposes.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.