Merge tip in Power Query

Hudson Andrew

New Member
Joined
Sep 28, 2016
Messages
31
Hi All,

My greeting to all the experts here in the forum , can some one pleSe hlep me how can i please make my merge process quick in power query .

I have two tables data sourced from Csv.files and each files contains 400 K plus rows of data , when i am doing merge process between both files to filter out old and new records .. during that process query refresh is taking ages to complete cause of i am adding a merge step.

Can some one help me how i can make the query refresh faster when there is merge step .. i could not upload the files cause those are massive to upload .
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
As you should know:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Merge Pro tip in Power Query [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Hi Alex,

Thanks for your reply , please find below M code ..
Power Query:
let

   WBPath = Excel.CurrentWorkbook(){[Name="WBPath"]}[Content]{0}[Column1],


    Source = Csv.Document(File.Contents(WBPath &"\621.csv"),[Delimiter=",", Columns=54, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"hu", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",15),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers- " i had to remove the columns name as it is data confidential .
(Graduate Trainees)" and [Region] = "India"),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows", each ([Region] = "India")),
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows3",{{"Candidate No", type text}, {"Stage Effective Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Job&Candidate No", each [Job RequisitionNo ]&[Candidate No]&[Stage Effective Date]&[Past Stage]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Job&Candidate No", type text}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type3", each [#"Job&Candidate ID"] <> ""),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows2",{"Job&Candidate ID"},#"MyList India",{"Job&Candidate No"},"MyList",JoinKind.LeftOuter),
    #"Expanded MyList" = Table.ExpandTableColumn(#"Merged Queries", "MyList", {"Record Type"}, {"Record Type"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded MyList",null,"New",Replacer.ReplaceValue,{"Record Type"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each [Record Type] = "New"),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Report Date", each Date.From(DateTime.LocalNow()))
in
    #"Added Custom1"
 
Upvote 0
Hi Rory, Yes please thanks for letting me know , this is noted :)
As you should know:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Merge Pro tip in Power Query [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem
 
Upvote 0
just wanted to share the link i cross posted with similar query in another forum

 
Last edited:
Upvote 0
Especially without data I am not sure that I am going to be able to help you.
But just to eliminate other issues.

1) Did it definitely get much slower only after you added the Merge step ?
There seem to be a lot of change type steps prior to that (I don't know if they make much difference to performance though but ideally unless certain functions need it you do this once)

2) If you remove the steps after the Merge does that make if faster.
If so try doing the replace as the last step. Apply the filters first just filter on null instead of New and filter on report date then replace Nulls with New.

3) I gather some people have had significant improvement by turning off "Always allow data previews to download in the background"

1624955088549.png
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top