Combine two different tables

[Anonymous]

Hello,

I have a problem I haven't been able to figure out. I have two files that I would like to combine in order to show my data better in my report. The two input files I have are File 1 and 2, and what I would like to do is Table 3. However I can't seem to create any relationship between Report day so wonder how I can get this to work smoothly? 

Thank you!

 

[Anonymous]

Hello both @Mariusz  & @amitchandak , thank you for the fast replies!

I realized my two tables were more "complex" than I showed here. If my lists/tables looks like this instead, how can I merge these two in the best way?

Thank you!

[Mariusz · Accepted solution]

Hi @Anonymous 

Sure, you can just create a date dimension join both tables on Report Day

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Later create Two Measures and add them to the table.

Trucks = 
CALCULATE(
    SUM( 'Table 4'[Qty] ),
    'Table 4'[Filter X] = 460, 
    'Table 4'[Yes / no] = "Yes",
    'Table 4'[Current / Non Current / N/A] = "N/A"
)

Car = 
CALCULATE(
    SUM( 'Table 5'[Qty] ),
    'Table 5'[Type 1] = 110, 
    'Table 5'[Type 2] = "YY"
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi again @Mariusz,

This worked like a charm, thank you very much!

[amitchandak]

Try like

table =
var _tab=
union(
summarize(table1, table1[Report Date], "car",0,"truck",sum(table[Qty])),
summarize(table2, table2[Report Date], "car",sum(table[deilvery]),"truck",0)
)
return
summarize(_tab,[Report Date],"Car",sum([car]),"truck",sum([truck]),"total",sum([car])+sum([truck]))

[Mariusz]

Hi @Anonymous 

You can do it in Power Query,

• Reference your table 2 
• Use Group By on Report Date and Type Of and Sum On Qty
• And later use merge queries on both tables 1 and 2

If you would like me to create a sample file then please provide both tables in a usable format.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn