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. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 2,346 guests
Please welcome our newest community members: