Forum Discussion

JazzyJeff43's avatar
JazzyJeff43
Copper Contributor
Jun 22, 2022

Extracting A Two Column List From the Blanks in a Matrix

Anyone know how to take a matrix where there are some blanks and turn just the blanks into a two column list? Example     a. b. c 1. X.     X 2. X 3  X. X To spit out: 1 B 2 B 2 C 3 C
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 22, 2022

JazzyJeff43 

A Power Query option (attached)

Query code (edited):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotedColumns = Table.UnpivotOtherColumns(Source, {}, "Name", "Value"),
    CountNullByColumn = Table.Group(UnpivotedColumns, {"Name"},
        {"NullCount", each Table.RowCount(Source) - Table.RowCount(_), Int64.Type}
    ),
    FilteredTable = Table.SelectRows(CountNullByColumn, each [NullCount] > 0),
    RepeatNames = Table.AddColumn(FilteredTable, "Names", each
        List.Repeat({[Name]}, [NullCount]),
        type list
    ),
    NamesToTable = Table.FromColumns({List.Combine(RepeatNames[Names])},
        type table [Blanks=text]
    )
in
    NamesToTable

 

 

 

Resources