Merge Summarize Table

[AllanBerces]

Hi to all, can someone help me if possible to Merge multiple Summarize table into one table

Output

Job	Task
rwer	ttytyt
werwer	tytytytyytyty
ertr	ghfghf
ertet	jghjgjgyt
564eyryt	ydrdryjtyjdtj
rtry	jtr6urjdj
fdhry5sysrt	dghdfgh6urt
65745ytjt	ueruytueu
ghjdtjj	hr754ysrtdun
2w4w4	nyw45 754ue

[freginier · Accepted solution]

Quick note on terminology first: in Power Query "Merge" means a join (it adds columns), whereas what you want here is to stack the rows from several tables into one - that's "Append" (Power Query) or UNION (DAX).

- If these summaries are Power Query queries: Home > Append Queries > Append Queries as New, then pick the tables. They share the same Job/Task columns, so you get one stacked table.

- If they are DAX calculated tables (created with SUMMARIZE): Combined = UNION(Summary1, Summary2, Summary3). UNION matches columns by position (so keep the same column order), takes the column names from the first table, and keeps duplicate rows - wrap it in DISTINCT() if you want to remove duplicates.

- If all three summaries come from the same base table, the cleanest option is to skip the intermediate tables and do a single SUMMARIZE/GROUPBY over the base table (or just drop Job and Task into one visual).

Hope that helps!

[cengizhanarslan · Accepted solution]

If these are separate SUMMARIZE results stored as variables or separate tables, you can combine them using UNION. Please try the logic below:

Combined Table =
VAR _Table1 =
    SUMMARIZE ( Source1, Source1[Job], Source1[Task] )
VAR _Table2 =
    SUMMARIZE ( Source2, Source2[Job], Source2[Task] )
VAR _Table3 =
    SUMMARIZE ( Source3, Source3[Job], Source3[Task] )
RETURN
    UNION ( _Table1, _Table2, _Table3 )

If you need this as a physical table (not a measure), use it inside a Calculated Table definition. If you are working in Power Query instead of DAX, use Table.Combine:

= Table.Combine({Table1, Table2, Table3})

[Lodha_Jaydeep · Accepted solution]

Hi @AllanBerces,

If you have the table with the same number of columns you can use the union to merge them all (as same your expected O/P) 

For existing tables.

Merged Table =
UNION (
SELECTCOLUMNS ( Table1, "Job", Table1[Job], "Task", Table1[Task] ),
SELECTCOLUMNS ( Table2, "Job", Table2[Job], "Task", Table2[Task] ),
SELECTCOLUMNS ( Table3, "Job", Table3[Job], "Task", Table3[Task] )
)

If you want to do the same in the power query editor you can use query something like, Assuming tables are already created.

let
    Combined =
        Table.Combine({Table1, Table2, Table3})
in
    Combined

Hope this is the asnwer to your question. Please consdier this as an accepted solution or give some kudos.

[AllanBerces]

Hi @Lodha_Jaydeep @cengizhanarslan @freginier thank you very much for the reply working all good.

[cengizhanarslan · Accepted solution]

If these are separate SUMMARIZE results stored as variables or separate tables, you can combine them using UNION. Please try the logic below:

Combined Table =
VAR _Table1 =
    SUMMARIZE ( Source1, Source1[Job], Source1[Task] )
VAR _Table2 =
    SUMMARIZE ( Source2, Source2[Job], Source2[Task] )
VAR _Table3 =
    SUMMARIZE ( Source3, Source3[Job], Source3[Task] )
RETURN
    UNION ( _Table1, _Table2, _Table3 )

If you need this as a physical table (not a measure), use it inside a Calculated Table definition. If you are working in Power Query instead of DAX, use Table.Combine:

= Table.Combine({Table1, Table2, Table3})

[freginier · Accepted solution]

Quick note on terminology first: in Power Query "Merge" means a join (it adds columns), whereas what you want here is to stack the rows from several tables into one - that's "Append" (Power Query) or UNION (DAX).

- If these summaries are Power Query queries: Home > Append Queries > Append Queries as New, then pick the tables. They share the same Job/Task columns, so you get one stacked table.

- If they are DAX calculated tables (created with SUMMARIZE): Combined = UNION(Summary1, Summary2, Summary3). UNION matches columns by position (so keep the same column order), takes the column names from the first table, and keeps duplicate rows - wrap it in DISTINCT() if you want to remove duplicates.

- If all three summaries come from the same base table, the cleanest option is to skip the intermediate tables and do a single SUMMARIZE/GROUPBY over the base table (or just drop Job and Task into one visual).

Hope that helps!