Forum Discussion

hejszyszky's avatar
hejszyszky
Icon for Helper II rankHelper II
4 years ago
Solved

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 & n...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    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])     )

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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]))

  • hejszyszky's avatar
    hejszyszky
    Icon for Helper II rankHelper II
    4 years ago

    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!

    • hejszyszky's avatar
      hejszyszky
      Icon for Helper II rankHelper II
      4 years ago

      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

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        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])     )