As long as the non-city levels use upper cases, it doesn’t matter how many region/sub-region levels there are before the country. With the approach applied above there is actually no individual exception filtering necessary.
=> 4 steps suffice to solve the issue:
let
Source = Before,
#"Merged Columns" = Table.CombineColumns(Source,{"Company", "Lvl1", "Lvl2", "Lvl3", "Lvl4", "Lvl5", "Lvl6"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"City"),
#"Added Country" = Table.AddColumn(#"Merged Columns", "Country", each if Text.Upper([City])=[City] then [City] else null, type text),
#"Filled Down" = Table.FillDown(#"Added Country",{"Country"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [City] <> [Country])
in
#"Filtered Rows"
4 Likes
That’s absolutely gorgeous, @Matthias ! The code cannot be more direct and simple than that! 
1 Like
Copied/Learnt some stuff from @Melissa and @BrianJ. Used some of my own experience of PQ/DAX and coding.
Code
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"jdRdauMwEADgq4Q8F3oGJRGut7Zl7HhDCX2Y2NpYa1ljJKWQi+UCudjKLN2fRNMWBgz5RpqRRmS/XyaZWLFs+bC8jdeH/fzlOWcEhShEtX3iVbHgTSVKTmSFqHd8wwsCZ/fYDj3qkfDkevHygPZIeA56vF4IbKbJgQZCM2WG62VSJrr37lEsPms+YM6qZ0JDrHGSpoejNEQCA/1+uFsNV7xjL/Tewmn8n2rRfHEoJUs/OFYOnVUdgSuwrdRoqFv9DlqaVlFcyzel4yMpw5tqEpYt6M4y5Q5oCCzReiSMjdChjZZNKs7XnK7JfC+NI3DbSxeeGBo1KKot8Bbu13NWb780K36XclthfqqVyFmRMiJjnluTJaz6nRHhuRL+CGOLPdJs9b53fOVWWTD3Sz/taW7r1PZgpfOEp+AUWXVUDsFGew6x1qefiwImbMkMNM6D8ZQn4c/D/ynPSra+zfm7Li02KSMsRH4aD6AI3EjdU7YCcwwPzErCn86dtHCAjvDYz9/CUQrC5ovF4YyEveCAPYxAsHAwUFbAEc9RfBYV/+Duaokn/Y+9/gI=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Company = _t, Lvl1 = _t, Lvl2 = _t, Lvl3 = _t, Lvl4 = _t, Lvl5 = _t, Lvl6 = _t]
),
BlankToNull = Table.ReplaceValue ( Source, "", null, Replacer.ReplaceValue, { "Company", "Lvl1", "Lvl2", "Lvl3", "Lvl4", "Lvl5", "Lvl6" } ),
ChangedType = Table.TransformColumnTypes (
BlankToNull,
{
{ "Company", type text },
{ "Lvl1", type text },
{ "Lvl2", type text },
{ "Lvl3", type text },
{ "Lvl4", type text },
{ "Lvl5", type text },
{ "Lvl6", type text }
}
),
GetNonNullList = Table.AddColumn (
ChangedType,
"NonNullList",
each
let
ToRecord = _,
ToList = Record.ToList ( ToRecord ),
RemoveNulls = List.RemoveNulls ( ToList )
in
RemoveNulls
),
GetContinent = Table.AddColumn (
GetNonNullList,
"Country",
each
let
ExtractValue = [NonNullList]{0}?,
ToUpperCase = Text.Upper ( ExtractValue ),
CheckCapsCase = if ExtractValue = ToUpperCase then ExtractValue else null
in
CheckCapsCase,
type text
),
FillDownCountry = Table.FillDown ( GetContinent, {"Country"} ),
GetCity = Table.AddColumn (
FillDownCountry,
"City",
each
let
ExtractValue = [NonNullList]{0}?,
ToUpperCase = Text.Upper ( ExtractValue ),
City = if ExtractValue <> ToUpperCase then ExtractValue else null
in
City,
type text
),
RemoveBlankCities = Table.SelectRows ( GetCity[ [Country], [City] ], each [City] <> null )
in
RemoveBlankCities
POTW # 8 - Hierarchy Problem.pbix (624.0 KB)
3 Likes
This thread is part of the Enterprise DNA platform initiative to provide information for our Problem Of The Week.
To ask any questions on the content covered in this category please start a new topic within the forum.
You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum
Hi all,
I wanted to find a solution that was not based on “UPPER case”, because sometimes with database the case is not really respected especially when the names are manually chosen.
So my solution is more about the structure of the data file.
I successively Group and Expand the columns in order to fill down the right rows.
Then to calculate Cities and Countries I’ve created conditional functions to have the two last levels that have values.
Here is my solution :
POTW # 8 - Hierarchy Problem - JBocher.pbix (2.9 MB)
Best regards,
Joaly
1 Like