Merge Queries With records that are different

[Anonymous]

Hi,

I have a table which contains the interaction of the students in a forum:

This table only contains the students ids who have participated in a forum depending on the course. I want to have both: students who participated and students that did not participate.

For that I have a table with all the students ids depending on a course :

So for example, in the fact_foro_discussion table:

• I want to have all the students from course 6, whether they have participated or not. Obviously i will know that if they have any value on preference column.
• Then all the students from course 5, whether they have participated or not. And so forth..

I also want to know if its better to do it in query editor or with in data model with DAX.

Resources: https://drive.google.com/drive/folders/1eYICNclX8KbAgI2fToO04aKHDzU-Zuga?usp=sharing 

The sample output is the excel file and you also have the power bi file.

Considerations:

• Rows on green color are the ones we already have in the fact table
• Rows on yellow color are the ones I need to add
• Be aware that students are added base on courseId
  • This students are added again if the DISCUSSION id changes.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?

[Anonymous]

Hi @amitchandak 

Refer to this files please: https://drive.google.com/drive/folders/1eYICNclX8KbAgI2fToO04aKHDzU-Zuga?usp=sharing 

The sample output is the excel file and you also have the power bi file.

Considerations:

• Rows on green color are the ones we already have in the fact table
• Rows on yellow color are the ones I need to add
• Be aware that students are added base on courseId
  • This students are added again if the forum or discussion id changes.

[v-yiruan-msft]

Hi @Anonymous ,

I updated your report file by creating measures based on your requirement, please check whether that is what you want. You can get the updated report file from this link.

In addition, you can also refer the following blog to merge the table in Power Query Editor to achieve it.

Merge Operations in Power BI / Power Query

Best Regards

Rena

[Anonymous]

Hi, thanks for your time @v-yiruan-msft .

It's working fine just for for the first section: CourseId = 5, Nforum = 8, Ndiscussion =2.

For CourseId= 6, Nforum =11, Ndiscussion =7, it should have the same pattern. In other words bring all the student id missing from courseId=6.

Then for CourseId = 6, Nforum=11, Ndiscussion=7, we repeat the pattern. Bring all the student id missing from courseId=6. Base on Nforum and Ndiscussion ids.

• Basically we group this in sections.  
• Every section is composed of Nforum and Ndiscussion
• We bring all the missing students ids from the courseId base on the section(Every time Nforum and Ndiscussion changes)

This is also explained on the excel file, which contains the sample output Data.

Could it be posible to do it in query editor or in data model?