Combining two datasets with similar data

[jasonwq]

Hi!

I have two sets of data. In each I have a column with States and a column with Salespeople. I'm just trying to combine them like you see in the third table below.

I want all data in all tables represented in the final result. Any tips? Thanks!

AP Customer
Bill State	Person
FL	Joe
GA	Sue
TX	George
OR	Cindy

Ship Location
Ship State	Person
FL	Joe
NC	Sam
SC	Theresa
CA	Jill

Combined
State	Person
FL	Joe
GA	Sue
TX	George
OR	Cindy
NC	Sam
SC	Theresa
CA	Jill

[parry2k · Accepted solution]

@jasonwq on modelling tab, click new table and use following DAX expression:

New Table = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( AP, "State", AP[Bill State], "Person", AP[Person] ),
        SELECTCOLUMNS ( Ship, "State", Ship[Ship State], "Person", Ship[Person] )
    )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[v-eqin-msft · Accepted solution]

Hi @jasonwq ,

You could refer to @parry2k 's solution based on DAX. And I will provide you another method which is realized in Power Query.

1. Right click in the blank field in Query Field-->Click New Query-->Blank Queries.

2. Open Advanced Editor window, paste the following M syntax.

let
    #"Renamed Columns1" = Table.RenameColumns(#"AP Customer",{{"Bill State", "State"}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Ship Location",{{"Ship State", "State"}}),
    #"Append"=Table.Combine({#"Renamed Columns1",#"Renamed Columns2"})
in
    #"Append"

Here is the final output:

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

[parry2k · Accepted solution]

@jasonwq on modelling tab, click new table and use following DAX expression:

New Table = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( AP, "State", AP[Bill State], "Person", AP[Person] ),
        SELECTCOLUMNS ( Ship, "State", Ship[Ship State], "Person", Ship[Person] )
    )
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡