Append

Hey Anonymous ,

if you want to append it in Power Query you have to import it first. After appending it, you can disable the load of the original Excel table. Like this the original Excel table will not be loaded to the data model.

If you don't want to do that in Power Query you have to do it in the data source where your table is. Like in SQL Server, mySQL, Sharepoint and so on.

Hi Anonymous 

You need to write some code in Power Query, for instance I created two excel files [File1 and File2] as following images:

Then I imported the file1 to the Power BI and open the Power Query, and changed the codes in the advaced Editor:

1- add the second file

2- align the columns names [in this exercise I just used Use First Rows as Headers [AKA "Promoted Headers"]

3- Append those 2 files

Codes are as below (Copy and pase this into the Advanced Editor on your computer and change file names and address)

let
    Source = Excel.Workbook(File.Contents("C:\Users\doustimajdv\Desktop\Test\File1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    A = Excel.Workbook(File.Contents("C:\Users\doustimajdv\Desktop\Test\File2.xlsx"), null, true),
    Sheet2_Sheet = A{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers1" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Appended Query1" = Table.Combine({#"Promoted Headers1", #"Promoted Headers"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query1",{{"A", Order.Ascending}})
in
    #"Sorted Rows"

the output will be as below:

Applied Steps:

Output Table:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

Hi Anonymous 

There are three commonly used import modes : Import , Direct Query , Live Connection . Different data sources support different loading modes . The default loading mode of some local files(Excel, Text…) is Import .

So if you don’t want load data to Desktop ,you need append your data in your data sources not in Desktop .In this case, you can choose some databases as your data sources , like :SQL ,MySQL , Oracle Database and so on .

I have an article that specifically introduces the import modes of different data sources and the differences between different modes. You can refer to the link below .

https://radacad.com/directquery-live-connection-or-import-data-tough-decision

Back to your problem , if you append an excel file to your table in Desktop ,you must import the data in Desktop and then append .

But there is a feature that can make you not see that table on the Report View .Click more options for the append table and select Hide in it .In your Report View ,you cannot see the table that from Excel file . The view effect is hidden, but the data is still loaded into the Desktop .

Best Regards

Community Support Team _ Ailsa Tao

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