cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
khetiwer Frequent Visitor
Frequent Visitor

using a calculated column in a calculated column

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!!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: using a calculated column in a calculated column

Hi,

 

Just use the Split column feature in the Query Editor.

ryan_mayu Established Member
Established Member

Re: using a calculated column in a calculated column

@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)

c1.JPG

 

3 REPLIES 3
Highlighted
Super User
Super User

Re: using a calculated column in a calculated column

Hi,

 

Just use the Split column feature in the Query Editor.

ryan_mayu Established Member
Established Member

Re: using a calculated column in a calculated column

@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)

c1.JPG

 

khetiwer Frequent Visitor
Frequent Visitor

Re: using a calculated column in a calculated column

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.