Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andrei
Regular 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

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. 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Can you please upload an example?

 

Thank you,

Andrei

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. 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

 

 

Andrei

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors