Search code examples
excelpowerquerydata-analysis

Excel: a matrix table with 1 and 0

If this is my data:

Name City Visited
Anna LA
Leo New York
Leo LA

how to create a table (preferably PIVOT table) like this in Excel:

New York LA
Anna 0 1
Leo 1 1

So the result needs to be something like a matrix table where 1 means a person visited the city, and 0 means a person didn't.

Solution

  • You could try the following using POWER QUERY

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name", "City Visited"}, {{"Count", each _, type table [Name=text, City Visited=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "City Visited", "Index"}, {"Name", "City Visited", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[#"City Visited"]), "City Visited", "Index", List.Count)
in
    #"Pivoted Column"

    enter image description here

    Or Using PIVOTBY()

    enter image description here

    =PIVOTBY(Table1[Name],Table1[City Visited],Table1[City Visited],COUNTA,0,0,,0)

    Using Pivot Table

    enter image description here