For this tutorial, I’m going to demonstrate how you can extract values before each occurrence of a specific text using Power Query Advanced Editor. There are many ways to achieve this, but I thought it would be fun to show a solution that’s fully coded. You can watch the full video of this tutorial at the end of this blog.
A member on the Enterprise DNA forum posted this scenario below. The fully coded solution that I’m going to demonstrate is more advanced, but I’ll break it down into very small pieces so we can examine the results of each individual step.
Using Power Query Advanced Editor & M Functions
I’ll create a reference of the sample query and add a custom column in which we can build our logic.
The first thing that I’m going to do is split that text column. So we’ll use the function, Text.Split, and then place an opening parenthesis. It wants a text, so we can point to our Text column. Next, it wants a separator as text. So in between a set of double quotes, we can add that semi-colon, then close the parenthesis, and press OK.
And with that, it gives us a list with separated text values.
We still have to do a bit of a cleanup because there could be some leading or trailing spaces included in these text strings and we need to get rid of those. That means we have to change the content of each of these lists, and there’s an M function for that called List.Transform.
So, in the formula bar, I’ll add List.Transform. It wants a list and we’ve given that, then put a comma. Next, is a transformation, so we want to trim those texts. So, I’ll add Text.Trim, then a closing parenthesis, and press OK.
Now, let’s open the Power Query Advanced Editor window because like I said, I want to break this up into really small pieces. I’ll use variables to store intermediate results for each of the steps.
We can create a comma-delimited set of variables inside a let expression. I’ll call the first step as ListTexts. That last closing parenthesis is of our table that added column functions, so move that to a new line as well. At the end of that first expression, we have to put in a comma, so we can add a new expression right there.
Then, we create a new variable name, which I call FindPositions. The entire string has been separated and cleaned. All we have to do is find the location for the string that we’re looking for. To do that, we’ll use a function called ListPositionOf. Add opening parenthesis, and we can return the variable that we already created (ListTexts).
Next, we put the value that we’re looking for, and that’s two capital A’s. The counterpart of let is the in the clause, so we need to add that and return it to FindPositions. Then, press Done.
This function takes an optional third parameter, so in the formula bar, we can add that third parameter. And with that, we get a list.
The goal is to extract the value before the search string, so we need to move back to a single position. But, we also have zeros. There is no value before the first initial value in that string, and we have to avoid errors, so we need to exclude those zeros, so we go back to the Advanced Editor again and add some logic.
We still need to move back to one position. And so, in the Advanced Editor, we can use List.Transform to help us out.
Next, we’ll use List.Transform to get strings and access items within our lists.
The final step is combining all the text values in the list into a single string. And so, I’m going to add another line in our logic for our result with the function, Text.Combine.
And that’s how you can extract strings before a specific text with a fully coded solution.
***** Related Links *****
Delimited Data – Extracting Records Using Power BI
Extract Values From Records And Lists In Power Query
Unpivot Multiple Columns Dynamically In Power BI
Conclusion
In this tutorial, you’ve seen how to extract strings before a specific text, and we used several texts and lists M functions to accomplish that task. I hope you’ve enjoyed this one. You can watch the full video tutorial below to see the step-by-step solution in detail.
All the best!
Melissa
[youtube https://www.youtube.com/watch?v=e50kYJu25OQ&w=784&h=441]
