Forum Discussion
Combine two tables
Hello,
I have created two calculated tables.
But I need to have only one.
Could you please tell me how can I combine them in one query?
I tried to use summarize, but it cannot see fileds of the variable.
Here are the codes:
table1 =
VAR SelectedSalesOrders =
SELECTCOLUMNS(
FILTER(
'SalesOrders',
'SalesOrders'[Date ordered] >= DATE(YEAR(TODAY()), 1, 1) &&
'SalesOrders'[SO Line type] <> "6"
),
"Company&SalesOrder&StockCode", 'SalesOrders'[Company] & 'SalesOrders'[Sales Order] & 'SalesOrders'[Stock Code]
)
VAR WIPjobsColumns =
SELECTCOLUMNS(
WIPJobs,
"Jobs", 'WIPJobs'[Job],
"Company&SalesOrder&StockCode", 'WIPJobs'[Company] & 'WIPJobs'[Sales Order] & 'WIPJobs'[Stock Code]
)
VAR LeftJoin1 =
NATURALLEFTOUTERJOIN(SelectedSalesOrders, WIPjobsColumns)
RETURN
distinct ( LeftJoin1)
Table 2 =
SUMMARIZE(
'table1',
'table1'[Jobs]
)
VAR CombinedTable = VAR SelectedSalesOrders = SELECTCOLUMNS( FILTER( 'SalesOrders', 'SalesOrders'[Date ordered] >= DATE(YEAR(TODAY()), 1, 1) && 'SalesOrders'[SO Line type] <> "6" ), "Company&SalesOrder&StockCode", 'SalesOrders'[Company] & 'SalesOrders'[Sales Order] & 'SalesOrders'[Stock Code] ) VAR WIPjobsColumns = SELECTCOLUMNS( WIPJobs, "Jobs", 'WIPJobs'[Job], "Company&SalesOrder&StockCode", 'WIPJobs'[Company] & 'WIPJobs'[Sales Order] & 'WIPJobs'[Stock Code] ) VAR LeftJoin1 = NATURALLEFTOUTERJOIN(SelectedSalesOrders, WIPjobsColumns) RETURN DISTINCT ( SELECTCOLUMNS(LeftJoin1, "Jobs", [Jobs]) ) // Or if you want to summarize based on Jobs and perform aggregation, you can do something like this: // SUMMARIZE(LeftJoin1, [Jobs], "SomeAggregatedValue", SUM('WIPJobs'[SomeColumnForAggregation])) RETURN CombinedTableYou need to perform the join and selection operations as you did in table1 and then immediately summarize the resulting table in the same query. However, since SUMMARIZE is typically used for grouping data and performing aggregations, and you seem to want to list Jobs uniquely, you might consider using DISTINCT if you're just listing distinct Jobs values. If you need additional aggregation, you can include those in the SUMMARIZE.
3 Replies
- AmiraBedhSuper User
VAR CombinedTable = VAR SelectedSalesOrders = SELECTCOLUMNS( FILTER( 'SalesOrders', 'SalesOrders'[Date ordered] >= DATE(YEAR(TODAY()), 1, 1) && 'SalesOrders'[SO Line type] <> "6" ), "Company&SalesOrder&StockCode", 'SalesOrders'[Company] & 'SalesOrders'[Sales Order] & 'SalesOrders'[Stock Code] ) VAR WIPjobsColumns = SELECTCOLUMNS( WIPJobs, "Jobs", 'WIPJobs'[Job], "Company&SalesOrder&StockCode", 'WIPJobs'[Company] & 'WIPJobs'[Sales Order] & 'WIPJobs'[Stock Code] ) VAR LeftJoin1 = NATURALLEFTOUTERJOIN(SelectedSalesOrders, WIPjobsColumns) RETURN DISTINCT ( SELECTCOLUMNS(LeftJoin1, "Jobs", [Jobs]) ) // Or if you want to summarize based on Jobs and perform aggregation, you can do something like this: // SUMMARIZE(LeftJoin1, [Jobs], "SomeAggregatedValue", SUM('WIPJobs'[SomeColumnForAggregation])) RETURN CombinedTableYou need to perform the join and selection operations as you did in table1 and then immediately summarize the resulting table in the same query. However, since SUMMARIZE is typically used for grouping data and performing aggregations, and you seem to want to list Jobs uniquely, you might consider using DISTINCT if you're just listing distinct Jobs values. If you need additional aggregation, you can include those in the SUMMARIZE.