Power Query - Text.Split based on variable in a different column

Question

Hi! I am trying to split a column based on the variable in another column. I have a dataset that mixes file paths with file names. I only want to split file paths that have files in them (indicated by the "Item Type" as Files). I think I need an if-statement and Text.Split. The green table indicates where I am starting and the orange one indicated where I want to end at.

Answer 1

Hi @DSmith

Assuming data in Excel Table1:

let  
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],  
    #"Changed Type" = Table.TransformColumnTypes(Source,  
        {{"File Path", type text}, {"Item Type", type text}}  
    ),  
    #"Added Files" = Table.AddColumn(#"Changed Type", "Files", each  
        if [Item Type] = "Folder"  
        then null  
        else Text.AfterDelimiter([File Path], "\", {0,RelativePosition.FromEnd}),  
        type text  
    ),  
    #"Added FilePath" = Table.AddColumn(#"Added Files", "FilePath", each  
        if [Item Type] = "Folder"  
        then [File Path]  
        else Text.TrimEnd(  
                Text.BeforeDelimiter([File Path], [Files], {0,RelativePosition.FromEnd}),  
                "\"  
            ),  
        type text  
    ),  
    #"Selected Columns" = Table.SelectColumns(#"Added FilePath",  
        {"FilePath", "Item Type", "Files"}  
    ),  
    #"Renamed As File Path" = Table.RenameColumns(#"Selected Columns",  
        {{"FilePath", "File Path"}}  
    )  
in  
    #"Renamed As File Path"  

Corresponding sample avail. here

Answer 2

Try adding a custom column with an expression like the following:

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