cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Kingpin
Kudo Kingpin

How to combine 2 separate M code script into 1 ?

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

 

 

1 ACCEPTED SOLUTION
Community Champion
Community Champion

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

View solution in original post

2 REPLIES 2
Super User II
Super User II

@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.

BA_Pete_2-1607176694611.png

 

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

BA_Pete_3-1607176759660.png

 

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:

BA_Pete_4-1607176965439.png

 

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

Community Champion
Community Champion

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

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors