[SOLVED] Power Query Custom Function to Retrieve a Section from Comma Delimited String

Power Query Custom Function to Retrieve a Section from Comma Delimited String

I am kinda new to Custom Functions.

I have an address column like the following:

De Villiers Street, Wellington North, Wellington, Western Cape, South Africa
Main Street, Paarl, Western Cape, South Africa

Some addresses have 5 sections and some have 4 sections.

I want to create a custom function in Power Query that I can use to retrieve a specific section from the address based on the how many sections it has.

    (Location,SectionNr) =>
    let 
        Sections = Text.Split(Location,","),
        Section = Sections[SectionNr]
    in 
        Section

For example to get the Province from the addresses above:

GetSection("De Villiers Street, Wellington North, Wellington, Western Cape, South Africa ",4)

... should return Western Cape

GetSection("Main Street, Paarl, Western Cape, South Africa",3)

... should return Western Cape

Solution

Figured it out:

= (Location,SectionNr) =>
let 
    Sections = Text.Split(Location,","),
    SectionValue =  Sections{SectionNr-1}
in 
    SectionValue

The problem was that I was [] brackets instead of {} brackets. Also because the list index start from 0, and I prefer to send the SectionNr as 1-n, I just subtract 1 from the SectionNr.