cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MK97
Frequent Visitor

Appending two data sources

Hello,

 

I need to append two data sources into one. The problem is that I need to get all data from first data source and only get that data from the second data source that does not exist in the first data source.

 

Data sources have different primary keys i.e.:

first data source: 00001#D, 00002#D, 00003#D

second data source: 00001#E, 00002#E, 00004#E

In the appended table i need to have these primary keys: 00001#D, 00002#D, 00003#D, 00004#E

 

I need to use similar function like in SQL - Where Exists

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @MK97 

 

First append two tables. Ensure the first data source table comes ahead in Table.Combine. Then extract id numbers before "#" into a new column. Select this new column and remove duplicates.

21100505.jpg

let
    Source = Table.Combine({#"Table A", #"Table B"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([ID], "#"), type text),
    #"Removed Duplicates" = Table.Distinct(#"Inserted Text Before Delimiter", {"Text Before Delimiter"})
in
    #"Removed Duplicates"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @MK97 

 

First append two tables. Ensure the first data source table comes ahead in Table.Combine. Then extract id numbers before "#" into a new column. Select this new column and remove duplicates.

21100505.jpg

let
    Source = Table.Combine({#"Table A", #"Table B"}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([ID], "#"), type text),
    #"Removed Duplicates" = Table.Distinct(#"Inserted Text Before Delimiter", {"Text Before Delimiter"})
in
    #"Removed Duplicates"

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

watkinnc
Super User
Super User

You can you the Merge function in the GUI, and choose Left Anti as the Join Kind. Then append this step to the step right before the join. 

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors