Forum Discussion

ColinJHarrison's avatar
ColinJHarrison
Brass Contributor
Nov 11, 2022
Solved

Filtering poorly formatted source data...

Hi, I have a weirdly arranged data set that I am importing into Excel via Power Query. My source has the structure I have indicated in the attached example (although the example is greatly simplifi...
BI & Data Analysis
excel
Formulas and Functions
power bi
  • ColinJHarrison's avatar
    ColinJHarrison
    Brass Contributor
    Nov 11, 2022

    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's avatar
        ColinJHarrison
        Brass Contributor
        Nov 11, 2022

        Riny_van_Eekelen 

        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

Resources