Slow performance with custom function applied to table

[fmerieux]

Hello everyone,

I have 3 different queries that contains list of articles and each query has a column that I want to apply to the other queries in order to have a comprehensive view of all the articles

I can not simply merge the queries, because some articles matches between queries but some articles don't and I need to keep them all

If you know a way to Merge+Append and fill in the same column I would love you till I'm dead!!

So the work around that I did is to append the 3 queries together, apply fillUp and FIllDown and then SelectRows  that I need in order to remove duplicates when case applies

I am using a custom function to be applied over a table from grouping rows :

   Table.Group(Append, {"Colum1","Column2"}, {{"All", each CustomFunction(_), type table }})

But I think that my function could be improve or there is something wrong with it:

(MyTable as table) =>

let
     #"FillUp" = Table.FillUp(MyTable,{"Column8"}),

    #"FillDown" = Table.FillDown(#"FillUp",{{"Column9"}),

    #"Keepit" = Table.SelectRows(#"FillDown",

         each [Level]<>0 or [#"Descript. synth."]<> null or [Index] = {Table.RowCount(TableDataConso)}[Index] )

in
#"Keepit"

I tried to Buffer some steps but I don't really understand how it works exactly and I did not get any major differences in performance

I am honestly lost with this and any help would be really appreciated

Thanks everyone

[fmerieux]

Thanks @ImkeF  for your reply

I thought about Left Outter join, but I don't know an efficient way to fill in columns of the left query with the right query that share the same names

Lets say I have "Column1" in both queries, after expanding my Outter Table, Column 1 will become Column1.1

I need to keep only Column 1 in the end but I have about ten columns like this and I'm afraid this would be worse than what I've tried

Any idea ?  Or am I missing something with the merge options ?

Thanks a lot

[ImkeF · Accepted solution]

Hi @fmerieux ,
have you considered full outer join options for the merges?: Full outer join - Power Query | Microsoft Learn
Otherwise, please post file with some mock-up data.
I currently specifically don't understand the effect of the filters you are applying here:

    #"Keepit" = Table.SelectRows(#"FillDown",

         each [Level]<>0 or [#"Descript. synth."]<> null or [Index] = {Table.RowCount(TableDataConso)}[Index] )
So very difficult to come up with alternative solutions here.