How to add a nw external table

[emma313823]

Hi All

I'm having a hard time figuring this out and need some direction.

I have a dashboard I created where I have connected it to an Access Database. Inside Access right now there is a 2020 table of data and I just added a 2021 table. Both tables are in the same ordinal position and have the same header rows.

In Power BI Desktop, I have only one visual - a table where I pulled in specific items from the 2020 access table. I now want to update this visual to include the data from the 2021 table, but don't understand how to make that happen. Any direction on how I can connect these two tables, so that the visual will display all that I have now, but include the new data from 2021? This will continue as years get added - i.e. table for 2022, 2023 etc.

Thanks

Emma

[parry2k · Accepted solution]

@emma313823 well you can append 2021 data before all the transformation and then all transformation will happen on both 2020 and 2021 data. if you are not sure how to do it, tell me if you would like to connect thru Teams.

[parry2k · Accepted solution]

@emma313823 well you can append 2021 data before all the transformation and then all transformation will happen on both 2020 and 2021 data. if you are not sure how to do it, tell me if you would like to connect thru Teams.

[emma313823]

Hi - if you have time, I would really appreciate the assistance in figuring this out. I'm still not understanding what you mean in that lost comment.

I'm on eastern standard time and use Teams also, but don't use the camera. I have time from now until 5 pm and I'm available tomorrow between 10 am and 1:30 pm. If these times don't work, let me know what times you have and hopefully something work. My email is sousae@crowleyassociates.com for Teams.

Emma

Emma

[Anonymous · Accepted solution]

Hi @emma313823 

In addition to parry2k‘s reply, I have a test to show you how to append two tables in Power Query Editor.

My Sample is as below.

2020:

I have renamed the column headers in Table 2020.

2021:

You need to transform 2021 as well.

If you use append directly the result will incorrect.

Rename column headers in 2021, and append two tables.

Then you got a new table2020-2021.

Best Regards,

Rico Zhou

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

[emma313823]

Hi thanks for the tip. Wondering if you can add to this a bit. In my 2020 data when I first started, I transformed some column headings to show a specific name when the data is refreshed from external source to Power BI Desktop. If following your instructions, do I also need to transform the 2021 data headings before I do anything?  I

I'm just trying to wrap my head around the steps involved.

1. I have to pull in the access table M2021 first and tranform that data so the headings line up - correct?

2. Select the M2020 table from within the tranform data screen?

3. Not sure how to connect 2020 to 2021?

4. Not sure how to append based on your comments

5. Not sure how to turn OFF 'enable load'

Emma

[parry2k]

@emma313823 in transform data, connect to 2021 table and append this in your existing 2020 table in Power Query. and turn of "Enable Load" for 2021 in PQ so that it doesn't show up in your model.

So, in the final, you will 2020 table in Power BI that contains both years data.