
Power BI

February 20, 2019

How can I add or duplicate rows when the value of a column is "Yes"? In this example, I wan to add or duplicate rows 7,8,9 based on "yes" on the Pge1.Split column.
Thanks!


July 16, 2010

Hi Jim,
One way is to duplicate the query and filter the Pge1.Split column to retain rows containing 'Yes', then merge the two queries As New. Or you could add a custom column:
= if [Pge1.Split] ="Yes" then "1-2" else null)
And split the new column by delimiter and in the advanced settings choose split into 'Rows'.
Mynda

Answers Post

New Member

January 20, 2021

Mynda, the second answer you gave:
Or you could add a custom column: = if [Pge1.Split] ="Yes" then "1-2" else null)
And split the new column by delimiter and in the advanced settings choose split into 'Rows'.
blew my mind!!!
Wow! Thanks so much! I registered in this forum just to tell you how grateful I am for the second solution you presented.
PS - you have fantastic YT videos. Thanks for all you do in teaching us.
Jase.

New Member

April 24, 2021

Hi,
Agree that this was very clever and worked for me:
if [Pge1.Split] ="Yes" then "1-2" else null)
I went a little further (pasted from advanced view):
#"Add number of rows" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Frequency]="Yes" then Text.Repeat("x",BlankCount) else null),
#"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Add number of rows", {{"Custom", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom"),
where BlankCount is a number from a named range in the spreadsheet - this number determined the number of repeats required. e.g. setting this as 3 creates a field value of "xxx"
Split is then by character (again creating rows) so each "x" created a new row.
1 Guest(s)
