Count of common values - Tricky

[Greatbi1]

Hi All,

Need urgent help.

I have data like below:

State | Engineer| 

Florida, Johny

Florida, Nick

Indiana, Johny

Indiana, Nick

Florida, Peter

Georgia, Peter

Indiana, Mike

Georgia, Mike

I want a pivot table like

[Anonymous]

Hi,

Use the Matrix table,

Which column you want in value table. Please click on the (down side arrow on value) select "Distinct or count" whichever you require.

Please refer the below print screen for your reference.

Thanks

[Greatbi1]

Hey thanks for answering, I was expecting that first column should be state. 

[camargos88 · Accepted solution]

Hi @Greatbi1 ,

Just merge the table with itself and filter the same states out:

the mcode is:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvJL8pMSVTSUfLKz8irVIrVQRbzy0zOBgt55qVkJuahKkOIwZUhdAaklqQWgcXcU/OL0jNRxRBafTOzU9GUQYRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, Engineer = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Engineer", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Engineer"}, #"Changed Type", {"Engineer"}, "Changed Type", JoinKind.Inner),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"State"}, {"State.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Changed Type", "Custom", each if [State] = [State.1] then 0 else 1),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
#"Removed Columns"

And use a matrix like:

[Greatbi1]

Thanks a lot @camargos88 . Awsome thinking. Hats off to you. I had spent more than a day on it. I can relax now. Thanks a ton.