Forum Discussion
Transfer one query from one data model to another
It's in the Advanced Editor. You were pasting M code so I figured you knew how to find it. Uploaded an example.
- one - this is an original query to a CSV built using the GUI
- two - another orginal query to a CSV built using the GUI
- Append1 - An "Append as New" query built using the GUI
- Query1 - This is a hand-built query that started with a Blank query. I copied and pasted the M code from the other queries and made the necessary edits as specified in the original article.
The important thing to remember when copying and pasting M code is to make sure that the references in each subsequent step refer to the correct previous step in the query. Also, watch your commas.
Sory,
I am a beginner at this.
Can't make any sense of your solution unfortunately. I am trying to append 2 files not merge them.
Are you opening a blank querry and then nest all those formulaes in one big formula to upload the files, promote headers and append them?
This is the M code I see while appending them in the classic way:
For File1
Source = Excel.Workbook(File.Contents("C:\Users\Andrei\Desktop\File1.xlsx"), null, true)
Navigation = Source{[Item="File1",Kind="Sheet"]}[Data]
Promoted Headers = Table.PromoteHeaders(File1, [PromoteAllScalars=true])
For File2
Source = Excel.Workbook(File.Contents("C:\Users\Andrei\Desktop\File2.xlsx"), null, true)
Navigation = Source{[Item="File2",Kind="Sheet"]}[Data]
Promoted Headers = Table.PromoteHeaders(File2, [PromoteAllScalars=true])
The append code is (for append as new):
Source = Table.Combine({File1, File2})
Or (for regular appending):
Appended Query = Table.Combine({#"Changed Type", File2})
In reality I have around 120 files that need to become one.
Can you please use the above info and advise me on how to nest all this in one formula that will do the trick?
Much appreciated.
Kind regards,
Andrei
Right, so start with a blank query and then use this:
let
Source1 = Excel.Workbook(File.Contents("C:\Users\Andrei\Desktop\File1.xlsx"), null, true)
Navigation1 = Source1{[Item="File1",Kind="Sheet"]}[Data]
PromotedHeaders1 = Table.PromoteHeaders(File1, [PromoteAllScalars=true])
Source2 = Excel.Workbook(File.Contents("C:\Users\Andrei\Desktop\File2.xlsx"), null, true)
Navigation2 = Source{[Item="File2",Kind="Sheet"]}[Data]
PromotedHeaders2 = Table.PromoteHeaders(File2, [PromoteAllScalars=true])
Append = Table.Combine(PromotedHeaders1,PromotedHeaders2)
in
Append
It's exactly the same concept as the article I sent you except that you are doing a Table.Combine instead of a Merge.
Where do you put all this code?
it is not letting me add 2 sources in the same query.
Kind regards,
Andrei
It's in the Advanced Editor. You were pasting M code so I figured you knew how to find it. Uploaded an example.
- one - this is an original query to a CSV built using the GUI
- two - another orginal query to a CSV built using the GUI
- Append1 - An "Append as New" query built using the GUI
- Query1 - This is a hand-built query that started with a Blank query. I copied and pasted the M code from the other queries and made the necessary edits as specified in the original article.
The important thing to remember when copying and pasting M code is to make sure that the references in each subsequent step refer to the correct previous step in the query. Also, watch your commas.
Thank you very much. You made my life allot easier.
Andrei
Can you please upload an example?
Thank you,
Andrei