Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Power Query M code for referring to another table

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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