Merge 2 queries and duplicate rows from one

[rmwwgs]

I have 2 queries I need to merge, but I need to replicate the rows from one query multiple times.

I am creating a matrix of students grades. At the start of the year they are given a target grade for each subject. They then have several reports throughout the year and I need to replicate the targets for each of those reports. 

So table 1 is laid out like this:

ReportCycle	StudentName	Subject	GradeName	Grade
1	StudentA	Art	Grade	C
1	StudentA	Maths	Grade	C
1	StudentA	English	Grade	C
2	StudentA	Art	Grade	B
2	StudentA	Maths	Grade	B
2	StudentA	English	Grade	B
3	StudentA	Art	Grade	A
3	StudentA	Maths	Grade	A
3	StudentA	English	Grade	A
1	StudentA	Art	Grade	C
1	StudentA	Maths	Grade	C
1	StudentA	English	Grade	C
2	StudentA	Art	Grade	B
2	StudentA	Maths	Grade	B
2	StudentA	English	Grade	B
3	StudentA	Art	Grade	A
3	StudentA	Maths	Grade	A
3	StudentA	English	Grade	A
1	StudentB	Art	Grade	C
1	StudentB	Maths	Grade	C
1	StudentB	English	Grade	C
2	StudentB	Art	Grade	B
2	StudentB	Maths	Grade	B
2	StudentB	English	Grade	B
3	StudentB	Art	Grade	A
3	StudentB	Maths	Grade	A
3	StudentB	English	Grade	A
1	StudentB	Art	Grade	C
1	StudentB	Maths	Grade	C
1	StudentB	English	Grade	C
2	StudentB	Art	Grade	B
2	StudentB	Maths	Grade	B
2	StudentB	English	Grade	B
3	StudentB	Art	Grade	A
3	StudentB	Maths	Grade	A
3	StudentB	English	Grade	A

And the second table looks like this:

StudentName	Subject	GradeName	Grade
StudentA	Art	Target	A
StudentA	Maths	Target	A
StudentA	English	Target	A
StudentB	Art	Target	A
StudentB	Maths	Target	A
StudentB	English	Target	A

Is there a way to merge them based on one or 2 columns so that it inserts a new row for every grade but with the target grade and name in the correct columns so it ends up looking like this for each student:

ReportCycle	StudentName	Subject	GradeName	Grade
1	StudentA	Art	Grade	C
1	StudentA	Art	Target	A
1	StudentA	Maths	Grade	C
1	StudentA	Maths	Target	A
1	StudentA	English	Grade	C
1	StudentA	English	Target	A
2	StudentA	Art	Grade	B
2	StudentA	Art	Target	A
2	StudentA	Maths	Grade	B
2	StudentA	Maths	Target	A
2	StudentA	English	Grade	B
2	StudentA	English	Target	A
3	StudentA	Art	Grade	A
3	StudentA	Art	Target	A
3	StudentA	Maths	Grade	A
3	StudentA	Maths	Target	A
3	StudentA	English	Grade	A
3	StudentA	English	Target	A

Thanks

[DataVitalizer]

Hi @rmwwgs 

Would the target be always A ? If so, throught power query add the following code to your query

let
Source = Your table,
#"1" = Table.TransformColumnTypes(Source,{{"ReportCycle ", Int64.Type}, {" StudentName ", type text}, {" Subject ", type text}, {" GradeName ", type text}, {" Grade", type text}}),
#"2" = Table.AddColumn(#"1", "Target", each "A"),
#"3" = Table.UnpivotOtherColumns(#"2", {" GradeName ", " Subject ", " StudentName ", "ReportCycle "}, "Attribut", "Valeur"),
#"4" = Table.RenameColumns(#"3",{{"Attribut", "Grade Name"}, {"Valeur", "Grade"}}),
#"5" = Table.RemoveColumns(#"4",{" GradeName "}),
#"6" = Table.TransformColumnTypes(#"5",{{"Grade", type text}})
in
#"6"

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated  

[rmwwgs]

No that was just an example, it could be anything and is pulled through from an SQL database.

[v-yalanwu-msft · Accepted solution]

Hi @rmwwgs ,

You could create a new table by the following formula：

newtable =
VAR _order =
    GENERATESERIES ( 1, 3, 1 )
VAR _crossjoin =
    CROSSJOIN ( _order, 'Table (2)' )
RETURN
    UNION ( 'Table', _crossjoin )

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rmwwgs]

This one works really well thanks!

The only problem I have now is that not all students are in all report cycles. The new table creates Target entries where there is no corresponding Grade entry for that cycle. Is there any way to filter that out in the UNION by specifying that there must already be a value for that column in the joining Table?