cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 ";Another" for each resource we have missing.

Source Table

Resources Allocated,Number Required,Resources Missing

Person1;Person2,3,1

Person2;Person4,2,0

Person3,3,2

 

Target

Resources Allocated, Number Required,Resources Missing,Resources Resource Allocated Ammended

Person1;Person2,3,1,Person1;Person2;Another

Person2;Person4,2,0,Person2;Person4

Person3,3,2,Person3;Another;Another

 

Thanks

Alex

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Specialist
Solution Specialist

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

 

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

 

View solution in original post

3 REPLIES 3
Highlighted
Solution Specialist
Solution Specialist

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

 

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

 

View solution in original post

Highlighted
Community Support
Community Support

Hi @athomp15 

If AlexisOlson slove your problem, could you kindly accept this as a solution?

If not, please let me know how to get target table from your original table?

 

Capture7.JPG

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Frequent Visitor

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors