none
Join two Excel tables using Power Query Formula Language (M) or Power Pivot (DAX) RRS feed

  • Question

  • Hi everyone,

    I have two tables in excel - one with date ranges (Period begin Date - Period End Date):

    Image

    and the other table I have is DimDate on day level of granularity (01-JAN-2010, 02-JAN-2010, etc.), created using this blog post from fantastic Chris Webb.

    What I want to achieve is to create either M or DAX script to join these two tables to get following result:

    Image

    SQL query should look like this:

    SELECT T.[Transaction Date], D.[Date] as [Period Date]
    FROM Transactions T
    INNER JOIN DimDate D ON D.Date BETWEEN T.[Period Begin Date] AND T.[Period End Date]

    Is something similar possible in M / DAX?

    Thanks,

    Thursday, November 19, 2015 3:19 AM

Answers

  • I could not access Bill's solution because of company firewall restrictions, but here is a solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}, {"Period Begin Date", type date}, {"Period End Date", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each Duration.Days([Period End Date]-[Period Begin Date])+1),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Period Date", each List.Dates([Period Begin Date],[Duration],#duration(1, 0, 0, 0))),
        #"Expanded Period Days" = Table.ExpandListColumn(#"Added Custom1", "Period Date"),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Period Days",{"Period End Date", "Duration"})
    in
        #"Removed Columns"


    Friday, November 20, 2015 6:23 PM

All replies

  • Yes, you want to create a query for both of these and then do a MERGE query:

    https://support.office.com/en-us/article/Merge-queries-Power-Query-FD157620-5470-4C0F-B132-7CA2616D17F9

    Thursday, November 19, 2015 2:37 PM
  • Hi Greg,

    Thank you for generic answer :) I know there is merge option in Power Query but in my case I need to join period and column, not two columns.

    Thanks,

    Thursday, November 19, 2015 10:37 PM
  • Hi John,

    Hmm.. when I look at your example of first table, i assume you want list of all days in month based on transaction date. If it is, then you do not need merge tables.
    This situation is the first one example in my attachment.
    If I'm wrong then look at the second one example. It uses a table from first example for merging with DimDate table.

    https://drive.google.com/file/d/0B6UlMk8OzUrxMUlHQ1lMMTJWaHM/view?usp=sharing

    Regards

    Thursday, November 19, 2015 11:48 PM
  • I could not access Bill's solution because of company firewall restrictions, but here is a solution:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction Date", type date}, {"Period Begin Date", type date}, {"Period End Date", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each Duration.Days([Period End Date]-[Period Begin Date])+1),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Period Date", each List.Dates([Period Begin Date],[Duration],#duration(1, 0, 0, 0))),
        #"Expanded Period Days" = Table.ExpandListColumn(#"Added Custom1", "Period Date"),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Period Days",{"Period End Date", "Duration"})
    in
        #"Removed Columns"


    Friday, November 20, 2015 6:23 PM
  • Bda75,

    Exactly what I need! Thanks!

    Now, for students who want to get A+ - how would you exclude weekends (Saturday & Sunday)? :)

    Monday, November 23, 2015 1:51 AM
  • Filter the "Period Date" column using the condition Date.DayOfWeek([Period Date])>5

    Monday, November 23, 2015 1:56 PM
  • Filter the "Period Date" column using the condition Date.DayOfWeek([Period Date])>5

    Adding first day parameter to make Saturday and Sunday the 6th and 7th day, respectively.

             Date.DayOfWeek([Period Date], Day.Monday)>5


    Friday, October 21, 2016 8:43 PM