Table filtering

[Anonymous]

Dear friends!

Could you help me with a simple question

I have a simple table : 

Name	Language : English	Language : French	Language : Russian
Adina	Intermediate	Basic knowledge	Advanced
Alexander	No competence	Intermediate	No competence
Ivan	Intermediate	No competence	Advanced
Max	Novice	No competence	Expert

Task is - to filter persons by skill and proficiency level,

1-st filtering by skill name( english) then level (expert)

Example: find person with expert russian language knowledge.

[Anonymous · Accepted solution]

Hi  @Anonymous,

After you load the data in the format you had pasted. Using the query editor you will have to unpivot the other Columns - Language : English , Language : French , Language: Russian

I have psted thee screen shot.

This will then create the table like

Now change the column name Attribute as Language, Value as Level

Then replace the Language : with null, that is to remove the word Language :

Save and Close.

With the table with just 3 columns Name, Language and Level. Use Language and Level in 2 slicers.  Create a Table visual with Name .

You will get the desired output.

Cheers

CheenuSing

[Anonymous]

Hi @Anonymous ,

If that is your table then the fields in your table should be :

Name 

Language 

Skill

if this is the case then all you would need to do is drag the Skill field on to the canvas and choose visual to be a slicer. This will allow you to select and filter on skill level.  however this will show for all Languages that has the skill that was selected. You can drag the language to the canvas and make it a slicer so that you can filter on the language and skill level.

Hope this helps

Kind Regards

Bash

[Anonymous · Accepted solution]

Hi  @Anonymous,

After you load the data in the format you had pasted. Using the query editor you will have to unpivot the other Columns - Language : English , Language : French , Language: Russian

I have psted thee screen shot.

This will then create the table like

Now change the column name Attribute as Language, Value as Level

Then replace the Language : with null, that is to remove the word Language :

Save and Close.

With the table with just 3 columns Name, Language and Level. Use Language and Level in 2 slicers.  Create a Table visual with Name .

You will get the desired output.

Cheers

CheenuSing

[Anonymous]

@Anonymous Dear friend !

Thank you for your answer !

unpivot helped me !

Have a nice day !

@v-diye-msft @Anonymous 

Dear friends , thank you for your answers too !!!

[Anonymous]

Dear @Anonymous ,

Languages in this table are just for example, i have table with a lot (~80) different skills.

I tried transposing the table to get a list of skills, but I cannot understand how to create separate filter with values ​​from a cell.

[Anonymous]

Hi @Anonymous ,

Could you show me the table in the data moddeling section?

Could you also show me the fields that you see on the report section ?

Kind Regards 

Bash

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

Please use the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckzJzEs8tEBJR8kzryS1KDc1JTOxJBXIdUoszkxWyM7LL89JTUkHiTimlCXmJaemKMXqAPXlpFYk5qWkFoH1+uUrJOfnFqSWpAIVYJqFKg3S7gk0ipA6NBt9EyvASsoyk7Gpda0oSC0qUYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Language : English" = _t, #"Language : French" = _t, #"Language : Russian" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Language : English", type text}, {"Language : French", type text}, {"Language : Russian", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Language"}, {"Value", "Skill"}})
in
    #"Renamed Columns

Pbix attached.