Power Query: Splitting a table based on column value.

[slk89]

Please note this question is based on Excel Power query.

After a merge operation (Full-Outer plus keep Only Non-matched), I have the table in the below format (Table1). My aim is to do an internal merge within the table, that should result in a table like Table2. 

My idea is to split the table based on column 'name'=null and then inner-join the resulting 2 tables based on 'name' and 'place'. 

Question : How to split the tables to 2 tables in powerquery based on a column value

Please suggest better ideas if any, in acheiving the below objective

[Anonymous · Accepted solution]

Hi @slk89 ,

I don't know if you have tried appending both tables directly to get the desired result., you can refer to

Append queries - Power Query | Microsoft Learn

If the above method does not work, you must start from Table 1 below.

You duplicate a table, and then romve columns and rename columns as follows.

Table1

Table2

In Table1, choose append queries.

You can filter out blank rows.

Expected result:

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.