Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hejszyszky
Helper II
Helper II

Creating new table based on other 3 tables values

Hi, 
My problem is as below.

I have table 1 structured like:
Date , Name , Value 
Table 2:
Date , Name , Value

Table 3 : Calendar (single values)

Table 1 and 2 contains multiple date & name values.

I would like to have a table 4 which contains summarized column value by Date(days) and Names as below
Date(Days), Name, Value(t1), Value(t2)

Could someone relate to this?

Thanks in advance!

1 ACCEPTED SOLUTION

@hejszyszky , best is append and group by in power query ,as ut can taken column that do not match too

 

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://docs.microsoft.com/en-us/power-query/group-by

 

or

 

 

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value",  0, "Value1", sum(Table1[Value1]) , "Value2", sum(Table1[Valu2])    ) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Value]) ,  "Value1",  0 ,"Value2",  0 )

)

 

return

Summarize(_tab, [Day], [Name], "Value", sumX(_tab,[Value]), "Value1", sumX(_tab,[Value1]) , "Value2", sumX(_tab,[Value2])     )

 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@hejszyszky ,

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value", sum(Table1[Name])) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Name]))

)

 

return

_tab

 

or

 

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value", sum(Table1[Name])) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Name]))

)

 

return

Summarize(_tab, [Day], [Name], "Value", sumX(_tab,[Value]))

Hi, Thanks for your response @amitchandak !

I forgot to add that the values refer to something different, so union wont work well here 😞 (names are the same tho)

My bad!

For better undestranding:

Table 1:
Date             Name Value1 Value2
1-11-2021    A           3           500

1-11-2021    A           4           50

2-11-2021    A           5           5
2-11-2021    B            6           5000

3-11-2021    A           7          5000

 

 

Table2:

 

Date             Name Value 
1-11-2021    B           3          

1-11-2021    B           5 

1-11-2021    A           4          

2-11-2021    A           5          
2-11-2021    B            6         

3-11-2021    C           7          

 

 

Outside of table 1 and 2 i would like to create Table3 as below:

 

Date           Name Value Value1 Value 2
1-11-2021  A         0           7        550

1-11-2021  B          8         0         0

2-11-2021  A         5          11       5005

2-11-2021  B          6          6        5000

3-11-2021  C          7          0           0

3-11-2021 A          0          7          5000

@hejszyszky , best is append and group by in power query ,as ut can taken column that do not match too

 

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://docs.microsoft.com/en-us/power-query/group-by

 

or

 

 

New Table =

var _Tab = Union(

Summarize(Table1, Date[Day], Table1[Name], "Value",  0, "Value1", sum(Table1[Value1]) , "Value2", sum(Table1[Valu2])    ) ,

Summarize(Table2, Date[Day], Table2[Name], "Value", sum(Table2[Value]) ,  "Value1",  0 ,"Value2",  0 )

)

 

return

Summarize(_tab, [Day], [Name], "Value", sumX(_tab,[Value]), "Value1", sumX(_tab,[Value1]) , "Value2", sumX(_tab,[Value2])     )

 

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors