How to combine 2 separate M code script into 1 ?

[Paulyeo11]

Hi All

I have 2 set of M code from seperate PBI files. 

File A = Master Calendar.

File B = Inventory Raw data Table.

I like to know how to combine both of them into 1 ?

So that File C become having 2 Table , Master Calendar and Inventory Table in one file.

Now what i do is i open PBI file A which i already have and manually step by step i import the raw data , which is very manual. like to find short cut.

File A M Code :-

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FS_AR_T/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Customer/Vendor Code", type text}, {"Customer/Vendor Name", type text}, {"Payment Terms Code", type text}, {"Outstanding w/o Tax (LC)", Int64.Type}, {"AR Invoice Number", type date}, {"AR Invoice Date", type text}, {"SlpName", type text}, {"Industry_Description", type text}, {"Sub-Industry", type text}, {"CreditLine", Int64.Type}, {"A/R amount include GST in FC", Int64.Type}, {"A/R amount include GST in LC", type number}})
in
#"Changed Type"

File B M Code :-

let
Source = SharePoint.Files("https://isdnholdings.sharepoint.com/sites/FT_INV/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Stock Code", Int64.Type}, {"Stock Description", type text}, {"Avail", Int64.Type}, {"OnHand Qty", Int64.Type}, {"Unit Cost", type number}, {"S_1", Int64.Type}, {"S_2", Int64.Type}, {"S_3", Int64.Type}, {"S_4", Int64.Type}, {"S_5", Int64.Type}, {"S_6", Int64.Type}, {"S_7", Int64.Type}, {"S_8", Int64.Type}, {"S_9", Int64.Type}, {"S_10", Int64.Type}, {"S_11", Int64.Type}, {"S_12", Int64.Type}, {"Prod_cls", Int64.Type}, {"Oh", Int64.Type}, {"Oh_1", Int64.Type}, {"Avail_2", type date}, {"Avail_3", type date}, {"L_issue", Int64.Type}, {"L_move", type text}, {"Qty_ord.", type date}, {"Long desc", type text}, {"Date", type text}, {"Drawwing number", type text}, {"Date stk", type text}, {"Description", type any}})
in
#"Changed Type"

Paul Yeo

[BA_Pete]

@Paulyeo11 ,

@PhilipTreacy 's solution will work fine for simple queries, but is a bit fiddly and runs the risk of bringing over incomplete query structures.

In your source PBIX, you should select/multi-select the queries that you want to copy, then right-click and select copy.

Then in your destination PBIX, right-click in the empty queries area and paste.

Firstly, this is quicker than opening advanced editor and creating new blank queries etc. Secondly, and most importantly, this maintains your query structure automatically.

In this example, I copied query 'dimImportance' and pasted into a blank PBIX. However, what you can't see is that this table is actually referenced from factTasks, but using this method of moving queries, the table that it is referenced from is also copied across automatically, therefore the query integrity is maintained:

This holds true for any type of linked query, such as queries that have been merged with and, I believe, even Table.Buffer() sources if they are separate queries too.

Pete

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

Just create a new PBI file and create 2 new blank queries.  Then copy/paste the code from File A into one of these queries,and copy/paste the code from File B into the 2nd query.

You should then have 2 queries in your new PBI file, each one containing one of the original queries shown above.

Phil