Search code examples
powerbislicers

How to make a default values

I have county names as dublin1, dublin2, dublin3 how to make a single default name as dublin to those values in power bi?

I have tried to remove duplicates but it is not a correct solution. I have tried concatenate as well but it will be useful to merge columns.

I got the Solution for Above question, I have one more query, Please find below table, first column is I am having data like that, second column which i need data like that.

Address 1: County Cuounty
Dublin Dublin
Co. Westmeath Westmeath
Co. Kerry Kerry
Dublin 13 Dublin
Co. Dublin Dublin
Dublin 6W Dublin
Co. Longford Longford
Solution

  • You will need a Table similar to:

    County County Variation
    Dublin dublin1
    Dublin dublin2
    Dublin dublin3

    You would then add a relationship on the second column to your data table(s). Lastly, you'd use the first column for your slicer.

    In PowerQuery, you could add a Custom Column with:

    Text.Proper(Text.Remove([Your Column], {"0".."9"} ))

    enter image description here

    Following on from the comments with additional asks. You can use this expression which will split the string by any of the following: . 0123456789, and return the longest portion.

    Text.Proper(
 List.Last(
  List.Sort(
   Text.SplitAny([County Variation], ". 0123456789"),
   (x, y) => Value.Compare(Text.Length(x), Text.Length(y))
  )
 )
)

    Version 2 to handle "Codublin":

    let
  txt = Text.Trim(Text.Lower([#"Address 1: County"])),
  checkCo = if not Text.StartsWith(txt, "cork") and Text.StartsWith(txt, "co") then Text.AfterDelimiter(txt, "co") else txt,
  result = Text.Proper(
    List.Last(
    List.Sort(
     Text.SplitAny(checkCo, ". 0123456789"),
     (x, y) => Value.Compare(Text.Length(x), Text.Length(y))
    )
   )
  )
in
  result

    enter image description here

    Ideally, you would have a finite list of County names, then loop through and match based on containing that name.