Is there a way to add an excel sheet onto a pre-existing table?

[dragonus]

So I have this table (call it Table1) where it has been formatted and converted into a long table.

I have this spreadsheet in Excel (call it SS1) where it is the same format from the original wide spreadsheet of Table1.

Was wondering if there's a way to append the data from SS1 onto Table1, applying all of its modifications made in Powerbi?

[MFelix · Accepted solution]

Hi @dragonus ,

On the query editor you can append several tables so this is possible you need to do the merge before the steps.

Assuming you have the following query:

• Source - Table 1
• Add columns
• Change Format
• Delete Columns
• Rename columns
• ...
• Last Step

What you need to do is to redo your query like this:

• Source - Table 1
• New source - Excel file
• Append Source with New Source
• Add columns
• Change Format
• Delete Columns
• Rename columns
• ...
• Last Step

The two bold steps are the ones that you need to do what I ussually do is one of two things:

• Create the new connection to the excel file and disable the load and do the append to this new connection
• Create the new connection and copy the source line (using advance editor) to the initial query and then make the append between the two step (source and new source) on the original query, then delete the connection I used for the copy+paste.

[mhossain · Accepted solution]

@dragonus 

Try below steps in powerquery:

--Import 'SS1' into your PBI and name the table it lets say 'SS1'

--Go to 'Table1' (existing table in pbi), and in toolbar find append option, there you can either append 'SS1' to existing 'Table1' or you can create new 3rd table and append 'Table1' and 'SS1' data there.

Hope this helps.

[v-deddai1-msft · Accepted solution]

Hi @dragonus ,

Just import SS1 into Power BI, select the source step in the applied steps of Table1, and then perform the append operation and select the SS1.

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

Best Regards,

Dedmon Dai

[mhossain · Accepted solution]

@dragonus 

Try below steps in powerquery:

--Import 'SS1' into your PBI and name the table it lets say 'SS1'

--Go to 'Table1' (existing table in pbi), and in toolbar find append option, there you can either append 'SS1' to existing 'Table1' or you can create new 3rd table and append 'Table1' and 'SS1' data there.

Hope this helps.

[MFelix · Accepted solution]

Hi @dragonus ,

On the query editor you can append several tables so this is possible you need to do the merge before the steps.

Assuming you have the following query:

• Source - Table 1
• Add columns
• Change Format
• Delete Columns
• Rename columns
• ...
• Last Step

What you need to do is to redo your query like this:

• Source - Table 1
• New source - Excel file
• Append Source with New Source
• Add columns
• Change Format
• Delete Columns
• Rename columns
• ...
• Last Step

The two bold steps are the ones that you need to do what I ussually do is one of two things:

• Create the new connection to the excel file and disable the load and do the append to this new connection
• Create the new connection and copy the source line (using advance editor) to the initial query and then make the append between the two step (source and new source) on the original query, then delete the connection I used for the copy+paste.