Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries