Forum Discussion

athomp15's avatar
athomp15
Helper I
6 years ago
Solved

Append to string based on number (loop)

Hi,    Any help would be appreciated.  I have a source table that shows allocated resources on a project. I would like to add a new column which appends to the "Resource Allocated" column ";Anothe...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    6 years ago

    You can do this with a custom column that repeats ";Another" according to the missing amount:

     

    [Resources Allocated] & Text.Repeat(";Another", [Resources Missing])

     

NSRjecross's avatar
NSRjecross
Frequent Visitor
6 years ago

athomp15 

I think the problem here is the original table design isn't designed for this type of reporting.

you should have a  project table that lists the project and the number of resources required.

Then a resource table with your resource items (apparently people)

Then you make a project-resource table with a record for each resource allocated to a project.

 

Then the operation you are after becomes joins with counts or pivots depending what you want.

At the moment you are having to unpack and repack your resource lists for allocated to get counts, who etc.

You can also add some dates to the various tables if you want to have project scheduling with intermittent allocation rather than have to assign a resource to the entire project timeline. 

Good luck

Jen