Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
It's in the Advanced Editor. You were pasting M code so I figured you knew how to find it. Uploaded an example.
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.
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
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.
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.