Reply
Frequent Visitor
Posts: 3
Registered: ‎01-11-2019
Accepted Solution

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


Accepted Solutions
Super User
Posts: 3,944
Registered: ‎01-14-2017

Re: using a calculated column in a calculated column

Hi,

 

Just use the Split column feature in the Query Editor.

View solution in original post

Established Member
Posts: 183
Registered: ‎09-17-2018

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

 

View solution in original post


All Replies
Super User
Posts: 3,944
Registered: ‎01-14-2017

Re: using a calculated column in a calculated column

Hi,

 

Just use the Split column feature in the Query Editor.

Established Member
Posts: 183
Registered: ‎09-17-2018

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

 

Highlighted
Frequent Visitor
Posts: 3
Registered: ‎01-11-2019

Re: using a calculated column in a calculated column

[ Edited ]

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.