Forum Discussion

SEC's avatar
SEC
Regular Visitor
4 years ago
Solved

One Slicer for multiple columns, with multiple items in some cells

Hello - I am trying to create a slicer that filters project data by staff name. The staff names 1) are listed in 3 different columns, and 2) there may be more than 1 name per field.    This is part...
Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    SEC this is a typical example to restructure your table and unpivot it. The best approach would be to unpivot the table and then split each team member's name into rows and from there it should be pretty straightforward.

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

SEC BTW here is teh Power Query code that can take care of it, and here is how the slicer will look like

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7BCsIwEER/JeQciEmr4rVCFcFTews9RAk2JGklySV/7y4l6mnfDOxjlKJDSdmERBm96adj5Kzj6iF1Js+QZh29SYyMNhBoB2jhjOVtiAAQXHC5kw2grDgxRe86OpPt8kKv9Si86FgY6aNeHBpsqB4J0PI9PiMeOG82RtHVxs3SrY+fC6f+zzjyts44fWdMHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Project Lead(s)" = _t, #"Project Member(s)" = _t, #"Project Team Lead(s)" = _t, #"Project Type" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Project Lead(s)", type text}, {"Project Member(s)", type text}, {"Project Team Lead(s)", type text}, {"Project Type", type text}, {"Start Date", type date}, {"End Date", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name", "Project Type", "Start Date", "End Date"}, "Team Type", "Team Member"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Team Member", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Team Member"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Team Member", Text.Trim, type text}})
in
    #"Trimmed Text"

 

Transform Data -> start a blank query -> click advanced editor and add above code, you can tweak it as you see fit.

 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

SEC's avatar
SEC
Regular Visitor
4 years ago

parry2k Thank you so much!!! This worked for me. The only issue I ran into is that once I transformed the data to make this work for the slicer, the other visuals I already had in my report no longer worked.

This may be a silly question (I'm a beginner), but would it be possible to have 2 sets of the same data in a report - 1 dataset that is transformed to meet this specific slicer need, and 1 dataset with the same data that is not transformed this way that I can use to create other report elements?

Or perhaps another solution?

Thanks!