Text.Split based on variable in a different column

[elmayimbe]

Hi! I am trying to split a row based on the variable in another column. I have a dataset that mixes file paths with file names. I only want to split the item in the column "File Path" only when the "Item Type" equals "File"). I think I need an if-statement and Text.Split but I am new to Power Query and M.

The green table indicates my starting point and the orange table indicates where I want to arrive.

[mussaenda]

Hi @elmayimbe, 

Are you able to achieve your requirement using @mahoneypat answer?

Or you need a sample power bi file with the solution?

[mahoneypat · Accepted solution]

You can add two custom columns with these expressions to get that result.

if [Item Type] = "File" then Text.BeforeDelimiter([File Path], "\", {0, RelativePosition.FromEnd}) else [File Path]

if [Item Type] = "File" then Text.AfterDelimiter([File Path], "\", {0, RelativePosition.FromEnd}) else null

Regards,

Pat