Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago

Combining two columns in a slicer

Hi there,

 

I have two columns located in two different tables. Tables do not have a direct relationship with each other. Both tables are dimension tables. The first table contains the city name and its code where as the second table contains the postal code. As one city can have multiple post codes, I want to show city code and postal code in a single slicer (in drop-down list) so that I can filter the report page based on city and postal code. Any help would be really appreciated. 

 

Data can be downloaded from here 

Need Help
Show and Tell
Tips and Tricks

7 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    5 years ago

    I lookedinto the file and looks like "City" sheet and "postal code" sheet do not have any commanality.

     

    If you join, it will be come cross apply. Is it what you are expecting?

    (Or) How do you know which postal code belongs to which city?

     

    Thanks

     

  • Dunner2020's avatar
    Dunner2020
    Icon for Post Prodigy rankPost Prodigy
    5 years ago

    sevenhills , I do not want cross apply. You are right that there is no commonality between two tables. In practice, data operator enters the data in the fact table and manually selects the city and postal codes from two drop-down list.  So a fact table row contains meaningful information about Area and postal code. 

    Is there any way of overcoming the issue?

    • sevenhills's avatar
      sevenhills
      Icon for Super User rankSuper User
      5 years ago

      Thanks, yes, two drop downs of city, postal code is common. other option is "city - postal code". 

      I am trying to understand the final result you want in the slicer ... 

       

      Do you want the data as like this?

       

      let
    Source = Table.Combine({CityTable, #"PostalCodeTable"}),
    #"Changed Type" = Table.TransformColumnTypes(Source, { { "Postal Code", type text }}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Postal Code"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"CityCode"}),
    #"Added Custom Column" = Table.AddColumn(#"Replaced Value1", "Custom", each Text.Combine({[CityCode], [Postal Code]}), type text) 
in
    #"Added Custom Column"

      shows as 

      5730

      724

      ...

      AT

      CK

      ... 

       

      or 

      something else in the slicer    ?

    • sevenhills's avatar
      sevenhills
      Icon for Super User rankSuper User
      5 years ago

      If it is me, I will do these steps.

       

      1. Create a common table and have all the combinations of CityCode, Postal Code, Slicer City Postal Code

       

      2. Link to the appropriate tables i.e., to the existing data tables

       

      3. Create the slicer using "Slicer City Postal Code"

       

      M language: 

       

       

      let
    Source = Table.Combine({CityTable, #"PostalCodeTable"}),
    #"Changed Type" = Table.TransformColumnTypes(Source, { { "Postal Code", type text }}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Postal Code"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"CityCode"}),
    #"Added Custom Column" = Table.AddColumn(#"Replaced Value1", "Slicer City Postal Code", each Text.Combine({[CityCode], " - ", [Postal Code]}), type text) 
in
    #"Added Custom Column"

       

       

      You may need to minor tweaks for your requirements .. hope this helps~

    • sevenhills's avatar
      sevenhills
      Icon for Super User rankSuper User
      5 years ago

      Yes, I mentioned as a common table and then do the slicer based on it.


      If this is not what you want, sorry!