Log In
Register
Home
Power BI
February 20, 2019
Offline
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!
See Answer
July 16, 2010
Offline
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
August 21, 2019
Offline
Hi Mynda
Amazing, didn't know can split into row with conditions, there will be a lot of use case for me
Thank you !
Jim
Attached an sample for you
July 16, 2010
Offline
Glad I could help, Jim 🙂
New Member
January 20, 2021
Offline
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
Offline
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)
