Add new column with highest region

[naveen73]

Hi All,

I have a dataset with section and categories. Please see a sample in the screenshot below:

As you can see, the more to the left you go the more in detail the columns get. What I want to achieve is to put the highest level in a new column “Region”. Please see the next screenshot.

Please see the link: 

https://docs.google.com/spreadsheets/d/1u100MnXlkrb2y-Q2wXjsP_EJ_sIY5roe/edit?usp=sharing&ouid=11029...

Thanks,

Naveen

[collinsg · Accepted solution]

Good day naveen73,

The steps below are:

1. Sort by "Highest Region" ascending and then by "Province" descending.
2. Add a "Region" column, if "Province" is "na" let it contain the country, otherwise null.
3. Fill down.

The interesting code starts at  #"Sorted Rows" - up to that point is just loading your sample data.

Hope this helps

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "bY5BCoMwEEWvUrJWSLyBltKu3LgMLgIZGkFnYBwX3r5jERpiF/MX8x787715MgS5dTxJmNBUptXDkMVYefPA9xwwnti6Ag8bM+w5tc798J1pj4RXrum+Rg+SgI+KVd/dvwk9saT6RfM55JBsU0jtsgpwDEsu2MYVhgr1QJukq6apk8YP",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [#"Highest region" = _t, Country = _t, Province = _t, City = _t, Town = _t]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"Highest region", type text},
            {"Country", type text},
            {"Province", type text},
            {"City", type text},
            {"Town", type text}
        }
    ),
    #"Sorted Rows" = Table.Sort(#"Changed Type", {{"Country", Order.Ascending}, {"Province", Order.Descending}}),
    #"Added Region" = Table.AddColumn(
        #"Sorted Rows", "Region", each if [Province] = "na" then [Highest region] else null, type text
    ),
    #"Filled Down" = Table.FillDown(#"Added Region", {"Region"})
in
    #"Filled Down"

[naveen73]

@collinsg thanks so much for this. Much appreciated!