Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
Solved! Go to 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.
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)
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.
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.
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.
To use your DAX, you just need to "New column" on your table outside of Query Editor.
Regards,
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |