Forum Discussion

naveen73's avatar
naveen73
Helper III
2 years ago
Solved

Add new column with highest region

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=110294040238289903125&rtpof=true&sd=true

 

Thanks,

 

Naveen

Need Help
  • collinsg's avatar
    collinsg
    2 years ago

    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"

2 Replies

  • collinsg's avatar
    collinsg
    Solution Sage
    2 years ago

    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"