using a calculated column in a calculated column

[khetiwer]

Hello.

I am working with a very large file to try to parse a column that has path strings like the following: /text/text/text/

The number of subsections for each path varies, and the length of the text inside the subsections vary. I was able to easily parse the first section by using 'find' and 'left' functions. However when I try to get to the second section using 'find' and 'mid' functions, it will not let me use the calculated column that I created to get the first section inside the new calculated column so that I know the starting point and end point of the mid functions. If I change the start and end to raw numbers, the function works. But because the path is varied, I need to have a dynamic start and end for my mid functions. 

I even tried to do a 'copy, paste value' like in excel, but there is of course no paste function. Any help would be greatly appreciated!!

[Ashish_Mathur · Accepted solution]

Hi,

Just use the Split column feature in the Query Editor.

[ryan_mayu · Accepted solution]

@khetiwer

Ashish provided you a good solution. If you only want to get a part of you text, you can try below coding.

Column = 
VAR first=FIND("/",Sheet2[test],1)
VAR second=FIND("/",Sheet2[test],first+1)
VAR thrid =FIND("/",Sheet2[test],second+1)
return MID(Sheet2[test],second+1,thrid-second-1)

[Ashish_Mathur · Accepted solution]

Hi,

Just use the Split column feature in the Query Editor.

[khetiwer]

Thank you both so so much! I have never used PowerBI to manipulate data tables, and only here because the file was too large for Excel.