Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Trying to add rows for missing months for each group

Hi!  Thanks for taking a look at this.

 

I have 2 tables.  One lists Organizations, has about 20 rows and one date field (the contract start month):

The other table is really just a list of all the "Start of month" dates from the beginning of the target period to today:

I've been trying to merge the two to get a table with a row for each month, for each Organization, from their initial contract start (from the Organization table) to today (from the date table) so I can see the revenue (MRR) for each month for each organization.  However, when I merge the two, I just get matching rows - no new rows.  I've read several responses to similar questions which all say this should work to add dates, but it seems to not work.

Any thoughts on what I'm missing, please?

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    According to your description, you want to combine two tables group by [org_id]  and generate the date from 'Organization'[contract_start]  to the Max of  'Date'[Strat of Month]. Right?

    You can realize it in Power Query Editor.

    Here are the steps you can follow:

    (1) This is my test data:

    (2)We can add a new Blank Query and we put this in "Advanced Editor" :

     

     

    let
    Source = Organization,
    Custom1 = Table.AddColumn(Source,"Date",(x)=> List.Select(Date[Start of Month],(y)=> y>=x[contract_start]  )    ),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"contract_start"}),
    #"Expanded test" = Table.ExpandListColumn(#"Removed Columns", "Date")
in
    #"Expanded test"

     

     

     (3)Then we can meet your need , the result is as follows:

     

    For dax , you can create a table and enter this:

    Table = 
var _t=CROSSJOIN('Organization','Date')
var _t2=FILTER(_t,[Start of Month]>=[contract_start])
return
SELECTCOLUMNS(_t2,"org_id",[org_id] , "Date" , [Start of Month])

     

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

7 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anonymous 

    According to your description, you want to combine two tables group by [org_id]  and generate the date from 'Organization'[contract_start]  to the Max of  'Date'[Strat of Month]. Right?

    You can realize it in Power Query Editor.

    Here are the steps you can follow:

    (1) This is my test data:

    (2)We can add a new Blank Query and we put this in "Advanced Editor" :

     

     

    let
    Source = Organization,
    Custom1 = Table.AddColumn(Source,"Date",(x)=> List.Select(Date[Start of Month],(y)=> y>=x[contract_start]  )    ),
    #"Removed Columns" = Table.RemoveColumns(Custom1,{"contract_start"}),
    #"Expanded test" = Table.ExpandListColumn(#"Removed Columns", "Date")
in
    #"Expanded test"

     

     

     (3)Then we can meet your need , the result is as follows:

     

    For dax , you can create a table and enter this:

    Table = 
var _t=CROSSJOIN('Organization','Date')
var _t2=FILTER(_t,[Start of Month]>=[contract_start])
return
SELECTCOLUMNS(_t2,"org_id",[org_id] , "Date" , [Start of Month])

     

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you!  That's exactly what I needed.  Thank you also for the detailed explanation - my goal is to understand this stuff so I don't need to ask so many questions - you really helped me with that, too!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This worked great for me too! What if there is a second condition? In this example, the only condition is

      y>=x[contract_start]

       

      What if you also want it to be less than contract_end? So something like this

       

      y>=x[contract_start] && y<=x[contract_end]

  • jbwtp's avatar
    jbwtp
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Sorry, what do you mean saying "new rows"? Does this mean the dates that are in the "revenue" table, but not in the "calendar" table or vise versa or both cases? This can be sorted by using a right join type outer, left or right.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I'm sorry I wasn't clearer - thanks for the response!  

      The "Revenue" table has just the first month of revenue (start month) as a date (first day of that month).  I want to add all the dates (first date of month) SINCE that time.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    =let a=Table.Buffer(AnotherTable) in Table.FromRecords(List.TransformMany(Table.ToRecords(OrgTable),each Table.ToRecords(Table.SelectRows(a,(x)=>x[Start of Month]>=[contract_start])),(x,y)=>x&y&[mrr=if x[contract_start]=y[Start of Month] then x[mrr] else null]))