cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Andrei Frequent Visitor
Frequent Visitor

Transfer one query from one data model to another

Hi,

 

I want to transfer an appended query from one data model to another but without transfering all the files that make up the appended file.

For example I have Query_1 and Query_2 and Append_all (which is Query_1 + Query_2). 

I need to copy Append_all to a different data model, but without importing Query_1 and Query_2.

Is there a way to do that? I am doing all this in Excel not in Power BI.

 

Thank you,

Andrei

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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. 

 

 


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

Proud to be a Datanaut!


8 REPLIES 8
Super User
Super User

Re: Transfer one query from one data model to another

Here's an article that might help. It's about Merge queries but same idea. Basically it is a matter of copying your M code from all of your different queries into a single query:

 

https://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx

 


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

Proud to be a Datanaut!


Andrei Frequent Visitor
Frequent Visitor

Re: Transfer one query from one data model to another

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

Super User
Super User

Re: Transfer one query from one data model to another

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.

 


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

Proud to be a Datanaut!


Andrei Frequent Visitor
Frequent Visitor

Re: Transfer one query from one data model to another

Where do you put all this code?

it is not letting me add 2 sources in the same query.

 

Kind regards,

Andrei

Andrei Frequent Visitor
Frequent Visitor

Re: Transfer one query from one data model to another

Can you please upload an example?

 

Thank you,

Andrei

Super User
Super User

Re: 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. 

 

 


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

Proud to be a Datanaut!


Andrei Frequent Visitor
Frequent Visitor

Re: Transfer one query from one data model to another

Thank you very much. You made my life allot easier.

 

 

Andrei

Andrei Frequent Visitor
Frequent Visitor

Re: Transfer one query from one data model to another

Hi,

 

Would you please be able to recommend some resources to learn more about Power Query, Advanced Editor and M Code.

I find it allot more powerfull then simple Excel.

Again, thank you for your help.

 

Kind regards,

Andrei