Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dvnnvd
Regular Visitor

Can I use a custom column and DAX to filter data in an existing column?

Hi Guys,

 

I have a column in my existing data that contains items in the following format Item 1 -> Item 2 -> Item 3

 

I need to create a custom column and use some sort of DAX formula so that the custom column only contains Item 1 -> Item 2 (removing Item 3). The items are not all the same length so I will likely need to use the "->" to seporate them so the forumula knows what to exclude.

 

Any idea how I can acheive this? In PowerPivot I used the below code, but I'm not sure how to reproduce this in PowerBI...

 

=LEFT([Name],IFERROR(FIND(" -> ",[Name],FIND(" -> ",[Name])+4),LEN([Name])+1)-1)

 

1 ACCEPTED SOLUTION

LEFT is a valid function in DAX, which is Power Pivot but not in the M language, which is Power Query. Seems like you are attempting to enter that formula in the Query Editor, which is Power Query. That is why you are getting the error. Technically, you could do this in Power Query, but the syntax would be a lot more difficult. So what you probably did was click on "Edit Queries." Instead, on the left most, black bar, there is a spreadsheet icon. When you click on that, it takes you to the data view. This is the equivalent of the power pivot window in excel. This is where you will enter your formula.

 

 

View solution in original post

5 REPLIES 5
TheOckieMofo
Resolver II
Resolver II

You should be able to use the exact same syntax in PBI. The data modeling engine in PBI is Power Pivot, just like you have in Excel. So the formulas are the same. YOu just need to add a custom column in your data model in Power BI. TO do that, go to "data" view in Power BI Desktop. Then click on the "modeling" tab. Select "new Column" which should create a formula in the formula bar that simplay sales "column =" Replace this with your column name then your formula. So it would be:

 

Falcons to the Super Bowl = LEFT([Name],IFERROR(FIND(" -> ",[Name],FIND(" -> ",[Name])+4),LEN([Name])+1)-1)

@TheOckieMofo

 

Thank you for the reply, however, I seem to get the below error:

 

Expression.Error: The name 'LEFT' wasn't recognized.  Make sure it's spelled correctly.

LEFT is a valid function in DAX, which is Power Pivot but not in the M language, which is Power Query. Seems like you are attempting to enter that formula in the Query Editor, which is Power Query. That is why you are getting the error. Technically, you could do this in Power Query, but the syntax would be a lot more difficult. So what you probably did was click on "Edit Queries." Instead, on the left most, black bar, there is a spreadsheet icon. When you click on that, it takes you to the data view. This is the equivalent of the power pivot window in excel. This is where you will enter your formula.

 

 

@TheOckieMofo

 

What I am doing is going into the data view, clicking the spreadsheet icon then clicking "Add Custom Column..."

 

This is then producing the "Add Custom Colmn" window prompting for name and formula - this is where I am entering the forumula, but it's still producing the same error.

 

Expression.Error: The name 'LEFT' wasn't recognized.  Make sure it's spelled correctly.

@dvnnvd

 

As @TheOckieMofo said, "Add Custom Column" in Query Editor is using Power Query (M language) syntax. You can use functions like "Text.Range" to achieve "substring" requirement.

 

34.PNG

 

To use your DAX, you just need to "New column" on your table outside of Query Editor.

 

23.PNG

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.