Merge two tables with different values

[Syndicate_Admin]

I have two tables that are generated from a database, which contain the counts of invoices issued and invoices processed.

In the first table, are the issued (the days column can change the values)

In the second, the defendants:

What I need to obtain, the combination of both, grouping the days of the column Day and Dia_factura in a single column, and to the right, the columns of Quantity Series, Total Series, Count Billed, Total Billed, more or less in this way:

Try using Merge queries, grouping by the day columns, but it does not take every day and when trying to do it with other types of combinations, it takes the days wrong and even duplicates the records:

I don't know what to do honestly, I'm new to PowerBI and I'm trying to learn as much as possible, but I haven't found a solution to this... I hope you can help me

[Syndicate_Admin]

I solved the problem with something so basic, but that I didn't think of at the time.
Create a table with specific values, called Days and in it you add the values from 1 to 31, which are the number of days you can have a month... Next, apply the relationship between the series and invoice table and you're done!!!
Everything flowed like oil!!!

I only have to drag the columns to the visual table and without having to combine the tables or any DAX calculation.

[Syndicate_Admin]

Someone who can give me a hand with this? I'm stuck with the matter...

[Ajith_Kumar]

Hi,

Please follow the following steps.

1. Merge the query using Full Outer Join.

2. Next Expand all the columns in table.

3. Create the custom column using the following code.

4. Rename the custom column name as Dia.

5. Reorder the colum and replace the null values in as 0 to value columns.

6. Please the below screenshot as solution.

Please mark this as solution. I hope you got the solution. 

[Thejeswar]

Hi @Syndicate_Admin ,

The Screenshot you have shared looks fine. I am not seeing any duplicates. The Numbers are looking fine.

Just that for 6 to 12, there are no entries in one tables, hence those columns are shown as null. Replace it with 0.

Also for Dia - 13 , if you see, the data is matcing as expected.

Also in your final post merge screenshot, remove column EstadoLectura. This will make the dataset like you want

Else share the sample data along with the issue causing scenario. that might help resolve this issue

[Syndicate_Admin]

But if you look, it does not show days 24 and 29 for example, which if they are in the table of "Master Day Invoice", is omitting, because in the table of Series are not those days.