Remove Columns from Table and then Remerge the Columns Later

[Anonymous]

Hi.

I am bringing in a table from Excel for processing. I would like to split this table into two separate tables with a common column as a merge key. The first table (with columns A, B, C, D) would be ingested into the processing, while the second table (with columns A, E, F, G) are separated for the sake of processing speed as these columns (E, F, G) are not necessary for the calculations. After the first table is processed, I would like to remerge the second table back into the first table with column A as the join key. How can I accomplish this task?

Any assistance would be greatly appreciated.

[v-stephen-msft]

Hi @Anonymous ,

Have you tried the Merge operation?

After tables are merged, you can expand columns what you want.

Reference: Combine queries

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jennratten]

Hello - below is how you can accomplish this.

Source Table: source_table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0cgaSrnnpOZnFGUCWhQGQiEwtVorVQVbgm1iSUQykzU2BhF8+mmxwcmZqXnIqkGWJrN0IyHZydkEx39QAbIACmgKECWamSCYYA9nOLq5IDjDBLo3QD7UALG8CZLq4uiFpR/EeyChXN3cU95kTUmBogF0FzAZLpBAyAzLd3D2QZM2gmhXQ5BHuN4e5PxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Student ID" = _t, #"Student Name" = _t, Subject = _t, #"Marks " = _t, Exemption = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student ID", Int64.Type}, {"Student Name", type text}, {"Subject", type text}, {"Marks ", Int64.Type}, {"Exemption", type text}})
in
    #"Changed Type"

New Table: Table1_ColAB

Create a reference of source_table that contains only the columns which are not needed for calculations and whose rows are unique.  In this example, only columns A and B are included.

let
    Source = source_table,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Student ID", "Student Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

New Table: Table2_ColACDE

Create another reference of source_table that contains the columns that are needed for calculations as well as columns that will be used as the join key.   Add the rest of your transformations here.  In this example columns A, C, D, E are included.

let
    Source = source_table,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Student ID", "Subject", "Marks ", "Exemption"})
in
    #"Removed Other Columns"

Merge Table2_ColACDE and Table1_ColAB (either as a new table or within Table2_ColACDE).  In this example, I merged as a new table, Table3_ColABCDE.  The results of matching rows from Table1_ColAB will be in a new column.  Expand the new column to add the values from Column B.

Final table:

let
    Source = Table.NestedJoin(Table2_ColACDE, {"Student ID"}, Table1_ColAB, {"Student ID"}, "Table1_ColAB", JoinKind.LeftOuter),
    #"Expanded Table1_ColAB" = Table.ExpandTableColumn(Source, "Table1_ColAB", {"Student Name"}, {"Student Name"})
in
    #"Expanded Table1_ColAB"