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

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

Glad I could help, Jim 🙂

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.

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.