Forum Discussion

dcormiernj's avatar
dcormiernj
Helper I
4 years ago
Solved

Need help to Merge two queries between date range

I have two tables very simply represented below Extract of employee table which has many columns but these are the two key ones for this issue.  each employee has a start date.  We track our weekly ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcrBEYAgDETRXjjjLFlJHIqwAoaDZdi9RERuP3lbayAETJQQex4jW6xBIMkvduhto10y8oT8jxwUZYJCdAH37bzu982y3gabe4Pwg/YA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week Start Date" = _t, #"Week End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week Start Date", type date}, {"Week End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Emp data", (x)=> x[Start Date]>=[Week Start Date] and x[Start Date]<=[Week End Date]){0}[Count], Int64.Type)
in
    #"Added Custom"

    CODE FOR Emp data table

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcvRCcAgDAXAXfItpHmatA7QKcT91zBVKIH8HtwY9AoVAgvjgtAsLkhSkzQXYakf4ZA6NbYgluR2Ue5Bni3QQN3J/jYX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Start Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Start Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Start Date"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

     Dowbload pbix file from here - https://1drv.ms/u/s!Akd5y6ruJhvhug7AWcPW9kkOhHHs?e=KoxVqn