Forum Discussion
Advice Needed: Data Modeling / Table Relationship
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 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!
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.
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.
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 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!