Forum Discussion

Andrei's avatar
Andrei
Regular Visitor
7 years ago
Solved

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

Need Help
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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. 

     

     

8 Replies

    • Andrei's avatar
      Andrei
      Regular Visitor
      7 years ago

      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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        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.