Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LABORSAL
Advocate II
Advocate II

Appending tables with complimentary data

Hello,

 

What I'm trying to achieve but been unable to so far is the join of 3 tables which compliment the data of each other based on a key value which must not be contained in the previous information.

 

As an example here is some dummy data (colored to make it easier to understand what's going on, middle table has 2 colors to separate the key value [Periodo] in the table)

Dummy DataDummy Data

In this example the join order should be blue/orange -> yellow -> green. When the orange table (versions in the second table) is not existant this is solved by doing a right anti between blue and yellow, mixing it with blue and then repeat the process with green, however when the version is introduced this no longer works.

 

What I've already tried is to group by the key which gives me a table of middle tables (if that makes sense) but when I try to do the previous steps I end up replicating everything and duplicates show up.

 

The end table should include (for each version) all the periods from middle, add possible periods from first and then add missing periods from gree, it would look like this:

End TableEnd Table

1 REPLY 1
amitchandak
Super User
Super User

Can you share sample data in table format and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.