Custom expanding rows based on other table data

[emmawdmi22]

Hello,

I'm fairly new to Power query, and I would appreciate some help with my current data.

Context: I have two tables, one called devices with deviceIds and their displayName (which can be "Machine", "Printer 1 & 2", "Printer 3 & 4", "Printer 5 & 6"). The other table, which is connected to it in the model by a one to many relationship, is called data. It has the matching deviceIds, a timestamp, and columns counter0, counter1, and counter2.

Issue: I want to be able to compare the counter data to each other in a new table that includes deviceId and counter (and displayName if that makes life easier for determining which value). I would like all deviceIds with displayName Machine to have counter = counter0 (from data table). For displayNames Printer 1 & 2, Printer 3 & 4, Printer 5 & 6, I want to make two rows for each input from the data table, one with counter = counter1 (for the odd # in the name) and one with counter = counter2 (for the even # in the name).

I don't know if this is even possible, but I thought I would give this a shot. I need to use powerquery and not dax for this because I have other grouping and manipulation to do afterwards. Thanks for your help!

[v-jingzhang · Accepted solution]

Hi @emmawdmi22 

Assume your original tables are like below, 

Is this what you want to have in a new table?

You can download the attached pbix at bottom to see detailed steps for the third table. I use Merge queries as new feature to create the new table.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[emmawdmi22]

This works perfectly! Thank you so much for your help!