Merge tip in Power Query

[Hudson Andrew]

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 .

 

[Alex Blakenburg]

Can your start with copying in the M Code from your Query or Queries.

 

[RoryA]

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.

 

[Hudson Andrew]

Hi Alex,

Thanks for your reply , please find below M code ..

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"

 

[Hudson Andrew]

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

 

[Hudson Andrew]

just wanted to share the link i cross posted with similar query in another forum

Merge Pro tip in Power Query [SOLVED]

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 tow tables data sourced from Csv.files and each files contains 400 K plus rows of data , when i am doing merge process between both...

www.excelforum.com

 

[Alex Blakenburg]

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"