Combining 2 tables

[sjoerdbuis]

Hi  see sample below

I have 2 tables, Company column combines the table.

How do I get a table combining Table 1 and 2 in table 3.

For my output I need a table 3 which looks at various breakdowns in the combined table.

The table 3 need to be used standalone.

[Ashish_Mathur]

Hi,

There are no Sector and Deal Type columns in Table 2.  Is that deliberate?

[v-juanli-msft · Accepted solution]

Hi @sjoerdbuis 

Code in Advanced editor of Table2_copy

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7PLUjMq1RwVNJRMjYwAJKW+sYG+kYGhhZAtlN+fnZYYk5pKpANohNLMvPzlGJ1EPqcgDImZOhzBsqYEqsvFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [company = _t, amount = _t, date = _t, transaction = _t, type2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"company", type text}, {"amount", Int64.Type}, {"date", type date}, {"transaction", type text}, {"type2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table1),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"company", "sector", "deal type"}, {"Custom.company", "Custom.sector", "Custom.deal type"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Custom", each if [company] = [Custom.company] then 1 else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [Custom] <> null and [Custom] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.company", "Custom"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"company", "Custom.sector", "Custom.deal type", "amount", "date", "transaction", "type2"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.sector", "sector"}, {"Custom.deal type", "deal type"}, {"transaction", "transaction"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns", Table1}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"amount", Order.Ascending}})
in
#"Sorted Rows"

Change source path with yours

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @sjoerdbuis 

Open Edit queries

1.Copy Table2, get Table2_Copy

The following steps are all in Table2_Copy

2.Add a custom column

Custom column formula

=Table1

3.select columns "company","sector","deal type" to expand

4.add a conditional column

5.filter "custom" column to remove empty

6.remove columns "Custom.company", "Custom"

7.reorder  (drag columns) and rename columns to make it order as Table1

8.in Table2_copy, append Table1 with Table2_copy

You could open my file to see details.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @sjoerdbuis,

1 Go to Query Editor.

2 Select Yor Teble 1
3 Find Append Queries in the Home Tab and click add as New, as below.

4 Select Your Tables as below, and klick OK.

Hope this helps 
Mariusz