Duplicating Rows in a Table in Modelling Tab

[LanrexTee]

Hello Team,

I have a Table (Table 1) with sample data below.

I would like to create another Table (Table2) from it with repeated rows based on RowCount Column below.

Note that Row with 'Pending' is repeated once for each Approval Month below under 'NewColumn' in Table2.

I've tried some proposed solutions but couldn't figure it out yet. I want to use the Table for further analysis.

Sample Data below.

Thanks

Lanrex

[amitchandak · Accepted solution]

In my formula.

Table 2 , I removed pending and added data. In table 3, I removed pending, Also check if pending exists, Hope you taken care of that in your formula, Or in visualization, put a visual level filter to remove pending.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[LanrexTee]

Sample Data Below:

Table1  
Approval Month RowCount 
2019-08               1 
2019-09               1 
2019-10               1 
2019-11               1 
2019-12               1 
Pending               5

Table 2(Output)

Approval Month RowCount NewColumn
2019-08               1                2019-08
2019-09               1                2019-09
2019-10               1                2019-10
2019-11               1                2019-11
2019-12               1                2019-12
Pending               5                2019-08

Pending               5                2019-09

Pending               5                2019-10

Pending               5                2019-11

Pending               5                2019-12

[amitchandak]

Check the link

https://www.dropbox.com/s/rbfswh4nbre14mk/duplicaterow.pbix?dl=0

Table 2 = SUMMARIZE(FILTER(duplicaterow,duplicaterow[Approval Month]<>"Pending" && COUNTX(filter(duplicaterow,duplicaterow[Approval Month]="Pending"),duplicaterow[RowCount ])>=1),duplicaterow[Approval Month],"Row Count",SUM(duplicaterow[RowCount ]),"New Column","Pending")

Table 3 = UNION(SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount ])),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[LanrexTee]

@amitchandak 

Many thanks for your prompt response...

the result is almost there with the tips you gave, but I got one row in that result which needs to be eliminated, there's the row that shows 'Pending " in the 'New Column'

Colud you have a look? i dont need that row in the result

Approval Month RowCount   New Column

2019-09              1              2019-09                 
2019-10             1               2019-10             
2019-11             1                2019-11             
2019-12             1                2019-12             
Pending             5                Pending 

2019-08              1               2019-08 

Pending              1               2019-09                  

Pending              1               2019-10  

Pending              1               2019-11   

Pending              1               2019-12  

Pending              1               2019-00     

Thanks

Lanrex                                                            

[amitchandak · Accepted solution]

In my formula.

Table 2 , I removed pending and added data. In table 3, I removed pending, Also check if pending exists, Hope you taken care of that in your formula, Or in visualization, put a visual level filter to remove pending.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[LanrexTee · Accepted solution]

@amitchandak 

Thanks a lot for your help....

I think the source of that error is the line I highlighted in red colour in the Union  where no filter is applied to remove the 'Pending'.

I did some modifications to that and it worked fine.

Table 3 = UNION(SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount ])),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])

So my modifications and final code by adding FILTER  with CALCULATETABLE is as per below :

Table 3 =
 UNION(
CALCULATETABLE(
    SUMMARIZE(duplicaterow,duplicaterow[Approval Month],"New Column",FIRSTNONBLANK(duplicaterow[Approval Month],""),"Row Count",sum(duplicaterow[RowCount])),
            FILTER(duplicaterow,duplicaterow[Approval Month]<>"Pending")),
SUMMARIZE('Table 2','Table 2'[New Column],'Table 2'[Approval Month],'Table 2'[Row Count])
)

Regards