Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add rows based on condition

Hi everyone,

I am looking for a solution to add new rows to my dataset.
For each Month of each value of [Country Name], I need to ensure that there is a row where [Business] = "Mining". If it does not in dataset, I need to add a new one. As in the picture below, there is row for every month of Country1, but only row for January and December of Country2. So I need to add more 10 rows (from February to November). However, it possibly required 3 or different number of rows for other [Country Name] value, or might be none (if like Country1).

I tried to divide this problem into small steps, both in Power Query or DAX, but till now I cannot find any reasonable solution. 

Any help from you would be so valuable to me!

Thank you!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    In Edit queries, i create a date table as below

    enter dates in box "startdate" and "enddate", then "Invoke", we can get a new table, renamed it as "date",

    Then in your table, if you only have three columns, group data as below,

    Then add a custom column and expand

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi Anonymous ,

     

     

    I think the easiest solution is the generate a new table in DAX with all the necessary rows.

     

    You can create a new calculated table in Power BI with the following formula:

    Country by month table = GENERATEALL(VALUES(Table1[Country Name]),VALUES(Table1[Calendar]))

    You can then add columns to this new table and copy there any values from your previous table.

     

    Does this help you?

     

    LC

    Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    In Edit queries, i create a date table as below

    enter dates in box "startdate" and "enddate", then "Invoke", we can get a new table, renamed it as "date",

    Then in your table, if you only have three columns, group data as below,

    Then add a custom column and expand

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    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
      6 years ago

      v-juanli-msft  thank you Juan li, there are much power query techniques on it and I have learned a lot. Very sorry for the late reaction.