Power Query M code for referring to another table

[CloudMonkey]

Hello,

Please can you tell me how to do a 'countifs' between two tables using Power Query?

In table 1 I have:

Project Start Date

Proejct End Date

In table 2 I have a calendar of flagged working days

Date

IsDateAWorkingDay

Using Power Query I'd like to add a column to table 1 to give the number of working days that a project lasted. It would equal the number of dates in table 2 that are greater than or equal to the [Project Start Date] and less than or equal to the [Project End Date] and where [IsDateAWorkingDay] is "True".

Please can you tell me if this is possible?

Thanks,

CM

[v-yulgu-msft · Accepted solution]

Hi @CloudMonkey,

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

[Anonymous]

@CloudMonkey

Yes, this is feasible.  The M solution is a little lengthy, but the Calculated Column method in DAX is a little faster (but it won't compress as well).

Table1[# of Working Days Column] =
CALCULATE (
    COUNTROWS ( Table2 ),
    FILTER (
        Table2,
        Table2[Date] >= Table1[Project Start Date]
            && Table2[Date] <= Table1[Project End Date]
    ),
    Table2[IsDateAWorkingDay] = "True"
)

I don't have the time to post the M solution right now, but let me know if you're interested in it.

[CloudMonkey]

Hi Chris - thanks but I would need to use Power Query / M code, so that I have the flexibility to be able to append tables later on.

Thanks,

CM