Transforming data with M
Now, there are a plethora of transformation functions that can be used within Advanced Editor to transform your data. With the issue/error we faced when trying to apply the filter, there are a certain number of functions we will need to use, such as Table.TransformColumns and Table.RemoveLastN.
As mentioned earlier, the first issue we can see in the data that might prevent us from filtering is that the values for cost and price contain a $ character. This is leading Power BI to read this as a text value. So our first port of call should be to remove this value from the column.
Now, of course, you could use the Split column function in the Power Query UI but it’s important to understand what M code is created behind the scenes from using such buttons. Using M will also help reduce the steps you need to get to the desired goal. This will particularly help when you’re looking to script more complex queries in M later in your data journey.
...