Forum Discussion
Duplicating Rows in a Table in Modelling Tab
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
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Thanks 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
5 Replies
Sample Data Below:
Table1
Approval Month RowCount
2019-08 1
2019-09 1
2019-10 1
2019-11 1
2019-12 1
Pending 5Table 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-08Pending 5 2019-09
Pending 5 2019-10
Pending 5 2019-11
Pending 5 2019-12
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Many 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 Pending2019-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