Related or Relatedtable or Addmissingitems? or something else?

[jbaich]

Hi all, pretty brand new to power query and data models (DAX) in excel, but I've been working hard over the last couple of weeks to learn as much as I can, however I'm currently stumped on this one and am hoping for a little guidance...

I have 2 tables; one Fact table that has a one to many relationship with the other dimension table. In the fact table, I have a column that contains many blank cells, some legitimate and others are blank because the report used to generate the table only populated values if they were exceptions... what i mean here is that if there was only 1 value option available for a particular record (ie. a default value) the report left the cell blank, if there were multiple options available in the DB, the user would have had to assign one as the predominate value and that would be included as a value in the report.

I am attempting to fill in the missing (default) values from the dimension table, which will contain multiple occurrences or matches of records in the fact table. Since RELATED would find multiple matches, i'm guessing that's not right... haven't had much luck experimenting with RELATEDTABLE OR ADDMISSINGITEMS either... So far, all I've really been able to accomplish is create a new calculated column in the fact table that counts the number of associated records in the dimension table for each record in the fact table... which doesn't really help.

Basically, what I'm thinking I need to do in terms of steps or formula expression are:

1. filter fact table to only apply the expression/formula to relevant rows (excluding non legitimate blanks and legitimate values).
2. count the number of unique values in the associated records in the dimension table for each filtered row (visible after step 1 filter applied) in the fact table.
3. If the count is 1 (this should be expected result) then "lookup" or import the related value from the dimension table... there may be multiple occurrences, but they should all hold the same value in that column.
4. If the count is not 1 (0 or greater than 1... this would be due to a mistake keying into the DB), then "No predominant record selected"

This is an example of the tables I'm working with....

Fact Table
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Pred. Cost Center[/TD]
[TD]Cost Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]101[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]235[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Sales[/TD]
[/TR]
</tbody>[/TABLE]
Other Cost Types would not be applicable so only need to apply expression to FILTERED records where Cost Type = "Sales"

Since there are multiples in the dtable and a value exists for this record in the fact table (101), it was designated as the predominate and returned. I want to keep this... so: =FILTER(fTable[Pred. Cost Center]=BLANK())?


Since Order #'s 2 and 4 are blank, there would have only been one 'default' value available for this record in the DB, so no predominate values would have been declared and blank cells were returned in the source report.


The goal is to populate the blank cells with the values from the dimension table below... there should only be one value option despite possible multiple occurrences, however there could be errors where this does not hold true... In this example, the blank cells for Order #'s 2 and 4 would be populated with the values 100 and 200 respectively from the table below.

Dimension Table
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Order #[/TD]
[TD]Cost Center[/TD]
[TD]Cost Type[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]101[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]102[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]200[/TD]
[TD]Finance[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]225[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]235[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200[/TD]
[TD]Inventory[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200[/TD]
[TD]Sales[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully this makes sense, sorry it got so long... I've been working of the microsoft library trying to learn all these new DAX expressions, but as a non programmer type, i feel like they couldn't have explained these in less laymen terms if they'd been trying...














any assistance would be very much appreciated!

Thanks

 

[horseyride]

Create two tables, Dimension and Fact

Load in Dimension table and remove duplicates with Dimension Query

Dimension Query

let
Source = Excel.CurrentWorkbook(){[Name="Dimension"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Cost Center", Int64.Type}, {"Cost Type", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Order #"})
in
#"Removed Duplicates"
​

Create Fact query to load in Fact table
Filter for completed items and call that Part1
Start over and filter for null then remove null column
Do Merge (LeftOuter) to Dimension query, expand, call that Part2
Merge Part1 and Part 2


Fact Query:

let
Source = Excel.CurrentWorkbook(){[Name="Fact"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Pred. Cost Center", Int64.Type}, {"Cost Type", type text}}),
Part1 = Table.SelectRows(#"Changed Type", each ([Pred. Cost Center] <> null)),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Pred. Cost Center] = null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Pred. Cost Center"}),
#"Merged Queries1" = Table.NestedJoin(#"Removed Columns",{"Order #", "Cost Type"},Dimension,{"Order #", "Cost Type"},"NewColumn",JoinKind.LeftOuter),
Part2 = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Cost Center"}, {"Pred. Cost Center"}),
Combined = Part1 & Part2
in
Combined
​

 

[jbaich]

I see, thanks! I'd been so focused on trying to use formulas or expressions similar to how i would have traditionally done things in excel that using queries and merging were not even on my radar! Gonna have to get used to thinking about things a little differently i guess.

Cheers,
Joe