Merge different Tables

[clo_64]

Hi,
my source is a folder with many csv files.
Each file has 2 tables with dynamic lengths and look like that:

NewCustomers

Cust A

Cust B

Cust C

...

BLANK line

LostCustomers

Cust D

....

BLANK line

Now when I import all these files in Power BI I get a long table with all these sub-tables:

NewCustomers

Cust A

Cust B

Cust C

...

BLANK line

LostCustomers

Cust D

....

BLANK line

NewCustomers

Cust A

Cust B

Cust C

...

BLANK line

LostCustomers

Cust D

....

BLANK line

...

How do I merge all the records from the 2 sub-tables and regroup all the data together to get just 1 Table with the new customers and one with the lost customers.

Thanks 

[serpiva64 · Accepted solution]

Hi,

you can simply use fill down on a duplicated column then append and filter (see the attached file)

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[serpiva64 · Accepted solution]

Hi,

you can simply use fill down on a duplicated column then append and filter (see the attached file)

If this post is useful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[clo_64]

that works nicely, and is straight forward.
An additional question: how do I split now the table that they appears as different tables in my Power BI (when I close Power Query). Do I need to keep the original query for New Customers and do a copy/paste to create a new query with a different Table.SelectedRows for the Lost Customer Table. Or there is something easier?  

[serpiva64]

Hi,

i don't know if i understand well your question.

I think you can also use a unique table (see the new file)

[clo_64 · Accepted solution]

No I really need to split in 2 tables, because these 2 tables have different fields. Sorry I did not want to make the explanation of the issue too complex and omited that.
My csv files looked like that:

NewCustomers
Name           Date          Product

Cust A          xyz             abc

Cust B

Cust C

...

BLANK line

LostCustomers

Name           Date          Reason

Cust D          ghj             blabla