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

LTAzua 1 Reputation point
2021-03-16T17:45:32.883+00:00

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.

78395-textsplit.png

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,591 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2021-03-17T04:05:39.09+00:00

    Hi @LTAzua

    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

    0 comments No comments

  2. Ehren (MSFT) 1,781 Reputation points Microsoft Employee
    2021-03-17T18:12:02.13+00:00

    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

    0 comments No comments