Forum Discussion
Merging Queries and then Pivoting?
I have two tables (Table 1 and Table 2) from the same Excel file into Power BI. This Excel file is updated weekly. What I've been trying to do--I am not sure if I'm going about it the right way-- is to unpivot and merge each table (Table 1 and Table 2) as a new query (New Merged Table)and do the necessary work from there.
- Will my original tables, now unpivoted (Table 1 Unpivoted and Table 2 Unpivoted) continue to update if I refresh them with updated data, after they've been unpipvoted?
- If I don't have relationships between these original tables and the New Merged Table, will the data continue to update in this new merged table with the data coming from the original two tables?
- Lastly, to keep all of the relevant rows for the Table1Value and Table2Value columns, would I want to use a Full Outer Join on these to preserve that? It's worth mentioning that I have a date lookup table that I can create a relationship with to help with any Week duplicates.
Table 1
| Week | Year | Area A | Area B | Area C |
| 1/1/2010 | 2010 | 5 | 7 | 8 |
| 1/8/2010 | 2010 | 4 | 7 | 9 |
| 5/11/2018 | 2018 | 3 | 4 | 3 |
Table 1 Unpivoted
| Week | Year | Area | Value |
| 1/1/2010 | 2010 | A | 5 |
| 1/1/2010 | 2010 | B | 7 |
| 1/1/2010 | 2010 | C | 8 |
| 1/8/2010 | 2010 | A | 4 |
| 1/8/2010 | 2010 | B | 7 |
| 1/8/2010 | 2010 | C | 9 |
Table 2
| Week | Year | Area A | Area B | Area C |
| 1/8/2010 | 2010 | .2 | .3 | .5 |
| 5/11/2018 | 2018 | .1 | .2 | .2 |
Table 2 Unpivoted
| Week | Year | Area | Value |
| 1/1/2010 | 2010 | A | .2 |
| 1/1/2010 | 2010 | B | .3 |
| 1/1/2010 | 2010 | C | .5 |
| 5/11/2018 | 2018 | A | .1 |
| 5/11/2018 | 2018 | B | .2 |
| 5/11/2018 | 2018 | C | .2 |
New Merged Table
| Week | Year | Area | Table1Value | Table2 Value |
| 1/1/2010 | 2010 | A | 5 | null |
| 1/1/2010 | 2010 | B | 7 | null |
| 1/1/2010 | 2010 | C | 8 | null |
| 1/8/2010 | 2010 | A | 4 | .2 |
| 1/8/2010 | 2010 | B | 7 | .3 |
| 1/8/2010 | 2010 | C | 9 | .5 |
| 5/11/2018 | 2018 | A | 3 | .1 |
| 5/11/2018 | 2018 | B | 4 | .2 |
| 5/11/2018 | 2018 | C | 3 | .2 |
Hi Anonymous ,
According to the test, when you refresh in Excel, both your original table A and original table B will be refreshed, and the new table AB formed by combining the original table A and the original table B will also be refreshed.
And P_A and P_B formed by Pivoting will also refresh the data, and the merge of P_A and P_B into a new table P_AB will also be refreshed.
If you want to keep all related rows of the Table1Value and Table2Value columns, you can use the JoinKind method of Full Outer in the merge to keep it.
Both the merging and pivoting steps are implemented after the data is loaded.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , Unpivoted/merge is the step added on the table you added to power bi, then they should continue work with refresh or new data
Hi Anonymous ,
According to the test, when you refresh in Excel, both your original table A and original table B will be refreshed, and the new table AB formed by combining the original table A and the original table B will also be refreshed.
And P_A and P_B formed by Pivoting will also refresh the data, and the merge of P_A and P_B into a new table P_AB will also be refreshed.
If you want to keep all related rows of the Table1Value and Table2Value columns, you can use the JoinKind method of Full Outer in the merge to keep it.
Both the merging and pivoting steps are implemented after the data is loaded.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
