Advice Needed: Data Modeling / Table Relationship

[win_anthony]

The goal is to have each transaction reflect a location that is associated with it. Each transaction is housed in a fact table and can be identified with a location through 2 columns: Owner / Entity. A Location dimension table was created and is connected to the transaction fact table through the store number. Problem is that when a transaction is identified by a district (district is 1 hierarchy level higher than store), I cannot get the associated transaction to reflect the district. Only specific transaction items will reflect the district as the location. It is ok that there is no store number associated with certain entities (ex: TACOS + BURGERS), the goal is to have the district number reflected if the entity reflect's district. Below you will find sample data for context: Location dimension table / current fact table data output / expected fact table data output. I know that my current set up is expected and the missing data (district number) would not show but how would you approach this if the requirement is to have it reflected? 

Any advice will be greatly appreciated. 

Location Dimension Table: This dimension table is linked to the fact table via Store_Number

District	District_Number	Store	Store_Number
Seattle (00101)	101	10001 - 1 Seattle St. Seattle, WA	10001
Los Angeles (00102)	102	10002 - 2 Los Angeles Blvd. Los Angeles, CA	10002
Austin (00103)	103	10003 - 3 Austin Dr. Austin, TX	10003
Chicago (00104)	104	10004 - 4 Chicago Ln. Chicago, IL	10004
New York (00105)	105	10005 - 5 New York Ave. New York, New York	10005

Current Fact Table Data: Notice only items TACOS + BURGERS does not have a district number being reflected. 

trans_id	item	district_number	store_number	entity	owner
1	Pizza	101	10001	Pizza Hut	Store Manager 10001
2	Sushi	101	10001	10001 - 1 Seattle St. Seattle, WA	Joe Rogan
3	Tacos			Chicago (00104)	Michael Jordan
4	Burgers			Austin (00103)	Elon Musk
5	Ramen	105	10005	10005 - 5 New York Ave. New York, New York	Store Manager 10005

Expected Fact Table: Notice that items TACOS + BURGERS now reflect the district number. It is ok that there is no store number associated with TACOS + BURGERS, the goal is to have the district number reflected if the entity reflect's district. 

trans_id	item	district_number	store_number	entity	owner
1	Pizza	101	10001	Pizza Hut	Store Manager 10001
2	Sushi	101	10001	10001 - 1 Seattle St. Seattle, WA	Joe Rogan
3	Tacos	104		Chicago (00104)	Michael Jordan
4	Burgers	103		Austin (00103)	Elon Musk
5	Ramen	105	10005	10005 - 5 New York Ave. New York, New York	Store Manager 10005

Current Data Model

[edhans · Accepted solution]

You definitely want to use the DIM table, but there is no way I can see to relate them.

In your top table, Taco/Burgers have no district or store. So how woudl they relate to a dim table. My offered advice was to populate the district in Power Query since the district seems to be embedded in the customer location - Chicago (00104) is district 104 per your 2nd fact table.

What am I not understanding?

[edhans]

You should use Power Query to put the district number there from the entity name.

if [district_number] = "" or [district_number] = null 
then 
   Text.TrimStart(
       Text.BetweenDelimiters([entity], "(", ")"),
       "0"
    ) 
else [district_number]

When done it looks like this:

Full code is here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDRCsIwDEV/JfRJYcqm7gOmCCJMxAkiYw9hhq1sttC1Cn69sdP5oFB6k9uccGmei0gEYi8fD2SNwsjfYTi4sHGW68xqQ5CiwooM9BNFkIvZ6811tfyhvcIEIsgIrW0JMjv91AGcEp7ZaoKDrlD5XXN2jljqjrU/q1qWWGkY8apwMWYnlWWN1MJWm8sbW7C9dIZzfcHEdVaqnpu/uHWrFaSuazwSs3PAKymfM36nHZRTx7CjO5y1aSC50XTogqH6+ymxKIon", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [trans_id = _t, item = _t, district_number = _t, store_number = _t, entity = _t, owner = _t]),
    #"Added Custom" = Table.AddColumn(Source, "New District Number", each if [district_number] = "" or [district_number] = null 
then 
   Text.TrimStart(
       Text.BetweenDelimiters([entity], "(", ")"),
       "0"
    ) 
else [district_number]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"district_number"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"New District Number", "district_number"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"district_number", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"trans_id", "item", "district_number", "store_number", "entity", "owner"})
in
    #"Reordered Columns"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[win_anthony]

@edhans thank you so much for your support! Question, are you suggesting that I do not use my Location dimension table? What I failed to mention is that my source SQL query yes has the District Number parsed out. Problem is that when I drop the Dimension District Number column into a table, the District Number is NULL. My Fact table does have the district number. 

The goal is to use the dimension table and have the district number column be reflected like shown in the expected output. All of this may be confusing but I hope you understand. Please let me know your thoughts. 

[edhans · Accepted solution]

You definitely want to use the DIM table, but there is no way I can see to relate them.

In your top table, Taco/Burgers have no district or store. So how woudl they relate to a dim table. My offered advice was to populate the district in Power Query since the district seems to be embedded in the customer location - Chicago (00104) is district 104 per your 2nd fact table.

What am I not understanding?

[win_anthony · Accepted solution]

@edhans thank you again for your support! Truly appreciated. I think you answered it for me. There is probably no possible connection for me to create by using the DIM table. I was hoping for some sort of workaround where I would be able to have the DIM[District Number] reflected on a table. 

Once again, thank you so much for your support!