What happens to Custom Columns added using Power Query on when new Rows are added during Refresh ?

[GopVan]

I have 2 Tables connected to 2 different Lists.  Each of these has different columns.  

Table A has 6 columns from the source and 2 generated.

1. ID

2. Position

3. Department

4. Year

5. Month

6. Interaction

7. Start Date(first day of the month generated by appending Year and Month and changing the type to Date)

8. End Date(generated using Date.EndOfMonth)

Table B has 

1.  ID

2. Start Date

3. End Date

4. Period Name

I am trying to append Table A and B and generate Table C.  Table B does not have Position and Department.

Should I

Option 1: Create 2 columns (Position and Department) in Table B using Custom Column and add a static value "All" and then append data. When new data is added on Refresh will those 2 new columns be populated with the static value before Appending or have null?

Option 2: Append Data and fill null values in Position and Department using other methods. 

When Data is appended I want to have one table with 6 columns with data from A and B. 

Table A                  Table B

ID                          ID

Position                 Position

Department           Department

Interaction             Period Name

Start Date              Start Date

End Date                End Date

1. Will Custom Columns have value populated on Refresh before the Append?

2. Since Interaction and Period Name don't match should I first rename the columns before Append?

3. Can I select columns to Append (how?) or Append all the columns and remove columns I don't need (e.g. Year and Month from Table A in this case)?

[v-henryk-mstf]

Hi @GopVan ,

Whether the advice given by @Thejeswar  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Thejeswar · Accepted solution]

Hi @GopVan ,

In Power Query, it is more like a Step by Step action that you are performing. i.e. The 2nd step will work on top of 1st step, 3rd on top of 2nd etc. So that latest step will be the applied on top of last but one step.

So it essentially depends on how and where you are placing your steps in Power Query.

It is best that you try the approaches and test with the scenario that you are expecting before finalizing on the approach to take rather than discussing the possibilities as this won't take much time to do.

3. Can I select columns to Append (how?) or Append all the columns and remove columns I don't need (e.g. Year and Month from Table A in this case)?

You can perform append using the below shown option in Power Query

You can append all the columns and remove those unwanted or you can do it vice versa. But removing unwanted columns before appending the tables is a better approach rather than appending and then removing the columns

Regards,