Related or Relatedtable or Addmissingitems? or something else?

jbaich

Board Regular
Joined
Nov 2, 2011
Messages
132
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
Order #Pred. Cost CenterCost Type
1101Sales
2Sales
3235Sales
4Sales

<tbody>
</tbody>
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
Order #Cost CenterCost Type
1101Sales
1102Sales
2100Sales
3200Finance
3225Sales
3235Sales
4200Sales
4200Inventory
4200Sales

<tbody>
</tbody>

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
 
Last edited:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

horseyride

Board Regular
Joined
Nov 2, 2017
Messages
82
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

Board Regular
Joined
Nov 2, 2011
Messages
132
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,110
Members
414,289
Latest member
sonintebil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top