How to combine two columns in Power BI

I don't know if the correct thing is to combine the columns but I have the following problem that I would like to solve with you and you can give me a better orientation and it is the following:

I have two excel sheets which I am using as a data source for a report in Power BI, one of these sheets has the following data:

SHEET 1:

And the other excel sheet with this data:

SHEET 2:

In the same report I want to represent the information of the two sheets, as you well know in Power BI each excel sheet represents it as a table, but when adding the filter of Location sheet 1 it does not bring me the values ​​of Area C, I understand that it's because I'm filtering by the column Location of sheet 1.

What I want to do is have a global column Location as a filter where the values ​​are found Area A, Area B, Area C, and when executing the filter they are dynamically applied to the data found in sheet 1 and sheet 2.

I don't see any point in having two Location filters with the only difference being that one has more values ​​than the other.

I don't know if the correct thing is to combine the two columns or make a many-to-many relationship, I hope you can guide me to make the best decision.

You should create a Location dimension table in Power Query that combines the values of Sheet1 and Sheet2. Assuming your existing tables are named "Sheet1" and "Sheet2", this Power Query would build the dimension:

let
    Source = Sheet1,
    #"Appended Query" = Table.Combine({Source, Sheet2}),
    #"Removed Other Columns" = Table.SelectColumns(#"Appended Query",{"Location "}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

Update: (steps to create the query above)

1. Right click your existing Sheet1 query and select Reference. This will create a new query that references the final result of the Sheet1 query. If the Sheet1 query is modified, this new query will get those changes too.
2. Then click Home > Append Queries
3. In the dropdown, select your query for Sheet2 and click OK
4. Right click the Location column and select Remove Other Columns
5. Right click the Location column again and select Remove Duplicates
6. If your data has any blank values, you should also filter those out using the dropdown on the Location column.
7. You should also change the name of the query to Locations

Then create a relationship between Locations[Location] and Sheet1[Location], as well as between Locations[Location] and Sheet2[Location].

Update: (Steps to create a relationship)

1. From the Power BI screen (not Power Query!), select Modeling > Manage relationships
2. Click the New... button
3. In the top drop down, select the table for Sheet1
4. In the second drop down, select the table for Locations
5. You should see the Location column selected for each table. Also make sure the Cardinality is set to "Many to one", the Cross filter direction should be "Single" and the checkbox selected for Make this relationship active. Then click OK Then repeat the steps above for Sheet2 and Locations.

In your reports, use the Locations[Location] column, not Sheet1[Location] or Sheet2[Location].