Return list of non-zeroes from 2-dim table

[mporter785]

Hi,

I would like to return a list of all non-zero values from this table and return as [[#row],[vendors]],[[#column]],count or split into three columns row,column,count which is preferred. Thanks

 

[lrobbo314]

Using Power Query.

Book10
	A	B	C	D
1	Row	VENDORS	Column	Value
2	10	HARTLEY	CT13	1
3	11	HATCH	CT13	1
4	12	HENDERSON	CT13	1
5	15	JACKMAN	CT13	1
6	16	KAEB	CT13	1
7	25	*	CT13	5
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Row", 1, 1, Int64.Type),
    UOC = Table.UnpivotOtherColumns(Index, {"Row", "VENDORS"}, "Column", "Value"),
    Filter = Table.SelectRows(UOC, each ([Value] <> 0)),
    Reorder = Table.ReorderColumns(Filter,{"Row", "VENDORS", "Column", "Value"})
in
    Reorder