question

LTAzua-1326 avatar image
0 Votes"
LTAzua-1326 asked Ehren answered

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

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


power-query-not-supported
textsplit.png (17.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

Hi @LTAzua-1326

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ehren avatar image
0 Votes"
Ehren answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.