Add rows based on condition

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

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]

@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.

[lc_finance]

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