Forum Discussion
Combine two different tables
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!
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
5 Replies
- Mariusz
Community Champion
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 - amitchandak
Super User
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])) 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
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
