Power Query M code for referring to another table

Hi Anonymous,

Please try below steps in Query Editor:

1) Suppose there is a Project column in table1. If not, please add an index column. Select the start date and end date columns in table1 and change data type to whole number.

2) add a custom column.

={[Project Start Date]..[Project End Date]}

3) expand to new rows and rename and convert back to dates

4) Merge table1 and table2 (Home -> Merge Queries). Expand necessary columns.

5) Filter [IsDateWorkingDay] to make tabe only show working dates.

6) Group table.

7) Result.

The entire M code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\xxxxx.xlsx"), null, true),
    table_1_Sheet = Source{[Item="table_1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(table_1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ProjectNo", Int64.Type}, {"Project Start Date", type date}, {"Project End Date", type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Project Start Date", Int64.Type}, {"Project End Date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each {[Project Start Date]..[Project End Date]}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"Project Start Date", type date}, {"Project End Date", type date}, {"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type2",{"Date"},table_2,{"Date"},"table_2",JoinKind.Inner),
    #"Expanded table_2" = Table.ExpandTableColumn(#"Merged Queries", "table_2", {"IsDateWorkingDay"}, {"table_2.IsDateWorkingDay"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded table_2", each ([table_2.IsDateWorkingDay] = true)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ProjectNo", "Project Start Date", "Project End Date"}, {{"CountWoringDay", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

Best regards,
Yuliana Gu