Combing Data Tables, Looking up Values and Filling in Blank Values

[LostintheBIu]

Hello everyone,

I would appreciate help with data transformation. I need to combine all values of 2 columns from 2 separate data tables, then do a look-up to another data table and finally fill in blank values.

There are three source data tables:

Source Data Table 1: Monthly Date Table (1st of each month)

Date

01/01/2022

01/02/2022

01/03/2022

01/04/2022

01/05/2022

01/06/2022

Source Data Table 2: List of Staff

Staff Name

John

Mary

Source Data Table 3: Staff Member's Department each Month

Date	Staff Name	Department
01/02/2022	Mary	Sales
01/02/2022	John	Finance
01/03/2022	Mary	Sales
01/04/2022	Mary	Marketing

Mary started working in February in Sales department, and moves to Marketing department in April.

John was only employed in February in Finance for one month

Desired Data Table:

Date	Staff Name	Department look up	Department look up - Blanks filled in
01/01/2022	Mary		Sales
01/02/2022	Mary	Sales	Sales
01/03/2022	Mary	Sales	Sales
01/04/2022	Mary	Marketing	Marketing
01/05/2022	Mary		Marketing
01/06/2022	Mary		Marketing
01/01/2022	John		Finance
01/02/2022	John	Finance	Finance
01/03/2022	John		Finance
01/04/2022	John		Finance
01/05/2022	John		Finance
01/06/2022	John		Finance

Requirements for the desired data table:

1) First we need to create a full combination table of all dates in the Monthly Date Table (Data Source 1) and all staff in the Staff List (Data Source 2)

2) Create the column "Department look up". This column does a lookup on Table Data Source 3 (Staff Member's Department each Month), looking up the combination of staff and date, and returning the department column.

3) Create the column ""Department look up - Blanks filled in". This takes the column "Department look up" and fills in any blanks.

This needs to be dynamic, as highlighted red in the table for Mary. If the time period is before she has any data (e.g. 1/1/2022), then it fills in with the next available value. If the time period is after she has any data (e.g. 1/5/2022), then it fills in with the previous available value. 

So there are quite a few steps involved, please let me know if you have any questions. I would really appreciate any help with this, thank you!

[MFelix · Accepted solution]

Hi @LostintheBIu ,

For this you need to do a couple of things I added a new table but you can do it based on the date table:

• Create a table referencing the date table
• Add a custom table with the Staff names
• Expand the column
• Merge with the staff / department based on date and staff
• Expand the column
• Group the table by staff name with the option of all rows selected (call this column StaffDetail)
• Add a new column with following code:

= Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown(
    Table.Sort([StaffDetail],{{"Staff Name", Order.Ascending}, {"Date", Order.Ascending}})

• Remove the StaffDetail
• Expand the column
• Do a Fill up based on department

See full code below:

let
    Source = Date,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Staff", each #"Staff Name"),
    #"Expanded Staff" = Table.ExpandTableColumn(#"Added Custom", "Staff", {"Staff Name"}, {"Staff Name"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Staff", {"Date", "Staff Name"}, #"Staff Department", {"Date", "Staff Name"}, "Staff Department", JoinKind.LeftOuter),
    #"Expanded Staff Department" = Table.ExpandTableColumn(#"Merged Queries", "Staff Department", {"Department"}, {"Department"}),
    #"Grouped Rows" = Table.Group(#"Expanded Staff Department", {"Staff Name"}, {{"StaffDetail", each _, type table [Date=nullable date, Staff Name=text, Department=nullable text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown(
    Table.Sort([StaffDetail],{{"Staff Name", Order.Ascending}, {"Date", Order.Ascending}})
      
    
    
    
    ,{"Department"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"StaffDetail"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Department"}, {"Date", "Department"}),
    #"Filled Up" = Table.FillUp(#"Expanded Custom",{"Department"})
in
    #"Filled Up"

[LostintheBIu]

Thank you so much @MFelix it worked perfectly!