How to add rows based on values in other table?

v-teven · ‎07-01-2018

Hi, I have two tables connected by the "attribute" field - which states the country, year, quarter and segment. One of the tables contains the "values" and these are unique entries by quarter and segment. The other table contains the "Measure" with multiple entries by quarter, month and day. My problem is that some quarters have no data in a spectific quarter, such as in this example: left table contains the measure, but misses attribute "Ireland-FY18-Q2-Corporate" because it has no entry (measure) for that specific country/time/segment; right table shows the missing entry.
How can I "add" those missing rows?

Anonymous · ‎07-01-2018

Lets name your tables as Table 1 ( with measures & row to be added) and Table 2 ( it has extra rows)

You can merge these tables in Power Query with Right outer join.

In Power Query -> Home -> Merge-> Merge Queries, Table1 as First table and Table 2 as 2nd table , join type is Right outer join.

Now, you will have the missing row, with null value in Measure field .

Thanks

Raj

v-teven · ‎07-07-2018

Thanks for your reply.

I want to avoid merging because the query of table 1 is too large and takes a long time to load. Also, I'm using it in the report for another tables with measures that are all connected. I think redoing everything again would give me more work than trying a different alternative. Any alternative suggestion would be really appreciated.

Thank you!