Forum Discussion
Filtering poorly formatted source data...
Riny_van_Eekelen as an output? Yes.
Are there formulas in your attachment? I'm looking at it on my phone, and I can't see any formulas.
ColinJHarrison No formulas. Just Power Query.
Hi Riny. I think your solution has shown me the way to solve my problem, so thank you very much! (Again!)
One question however - do you write your M code directly, or is everything you've put in this solution achievable using the input wizards provided by Power Query? How for instance did you build your custom column call:
= Table.AddColumn(#"Removed columns", "Subsection", each if Text.Contains([Column1], "Region -") then [Column1] else null)At this stage I would have no idea how to hand code someting like that, and I'm just wondering how much of https://learn.microsoft.com/en-us/powerquery-m/understanding-power-query-m-functions I need to be on top of to come up with solutions like this myself... 🙂
Cheers
ColinJHarrison You can get quite far in PQ by only clicking on the correct buttons and fill in the options that pop up. But, at some point you need to learn some M-coding in order to make more complex transformation. Not difficult, but it takes time to learn.
The line of code you refer to isn't really that special. On the Add Column ribbon you click Custom column.
In the window that opens, you type the name you want to give to the new column in the designated space and then the formula:
if Text.Contains([Column1], "Region -") then [Column1] else null
in the box below. And OK.
PQ then comes up with the rest of the code. I.e. using the function Table.AddColumn, the previous step name, the new column name, the 'each' bit and the closing bracket.
By reviewing the whole code, however, you'll soon figure out how it works and how to manipulate it. And then you'll find plenty of resources on line with practical examples of the various M-functions.
Riny_van_Eekelen ah ok. Don't think I can open that on my phone! I'll check it out when I'm home. Thanks Riny! 👍