Reply
Merleau
Helper II
Helper II
Partially syndicated - Outbound

Repeat columns based on values from another table

Hello,

I have the following tables: TableA and TableB

 

TableA

IdR1AMR2AMR2PMR3PMR4PMCountAMCountPM
id11000111
id20011012
id31111032
id40001021

 

TableB:

ProgramDuplicate
R1PM2
R2PM3
R3PM3
R4PM4
R5PM4

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:

IdR1AMR2AMR2PMR2PMR2PMR3PMR3PMR3PMR4PMR4PMR4PMR4PM
id1100000001111
id2001111110000
id3111111110000
id4000001110000

 

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.

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Merleau 

Please check M queries in My pbix.

If you have any problem or need detailed steps, feel free to let me know.

Capture13.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.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Syndicated - Outbound

Hi @Merleau 

Please check M queries in My pbix.

If you have any problem or need detailed steps, feel free to let me know.

Capture13.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.

Syndicated - Outbound

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.

Greg_Deckler
Super User
Super User

Syndicated - Outbound

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...
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)