- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Repeat columns based on values from another table
Hello,
I have the following tables: TableA and TableB
TableA
Id | R1AM | R2AM | R2PM | R3PM | R4PM | CountAM | CountPM |
id1 | 1 | 0 | 0 | 0 | 1 | 1 | 1 |
id2 | 0 | 0 | 1 | 1 | 0 | 1 | 2 |
id3 | 1 | 1 | 1 | 1 | 0 | 3 | 2 |
id4 | 0 | 0 | 0 | 1 | 0 | 2 | 1 |
TableB:
Program | Duplicate |
R1PM | 2 |
R2PM | 3 |
R3PM | 3 |
R4PM | 4 |
R5PM | 4 |
Names of column ending with "PM" in TableA are a subset of "Program" in TableB.
I have to update TableA by using information from TableB.
Reading each "program" in TableB, I check if the program (name of column ) in TableA exist.
If yes, I repeat that column in tableA x number of times. x is the corresponding "replicate" number from TableB.
My result should be the updated TableA below:
Id | R1AM | R2AM | R2PM | R2PM | R2PM | R3PM | R3PM | R3PM | R4PM | R4PM | R4PM | R4PM |
id1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
id2 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
id3 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
id4 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
Then compute some stats such as counting entries in all column ending with "PM" ( result = 19 )
Can somebody pls help?
Any suggestion is welcome.
Thank you.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Merleau
Please check M queries in My pbix.
If you have any problem or need detailed steps, feel free to let me know.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Merleau
Please check M queries in My pbix.
If you have any problem or need detailed steps, feel free to let me know.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This is brilliant!
Thank you so much.
However, I had to pivot it back to compute the different total. I finally insert it as a table in my report.
This way, I have the freedom to arrange the columns in a particular order. As a matrix, I didn't know how to do it.
Again, I appreciate the help.
Thank you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Maybe it is because it is all squished together and hard to read but I have no clue how you are getting from A to B (source to expected ouput). Can you please explain in plain language the logic?
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...

Helpful resources
User | Count |
---|---|
32 | |
18 | |
14 | |
11 | |
8 |