How to automate changing a table to a structured list and exclude blanks

[gullinnova]

My goal is to take a table with any # of rows but limited number of columns like this:

And have it convert to this format with power query: 

etc

it omits any value <1 and iterates over all instances of column1 that exist. 

I've done about 4 hours of my own research trying to find out but having a hard time. Hoping an expert here can enlighten me. Thank you for your time

[adudani]

hi @gullinnova ,

please provide a sample input file removing sensitive data?

[gullinnova]

Item Number	store1	store2	store3	store4	store5	store6
195553951579						1
195553964852		2	2	2
197105004375						8
195553327275	1		1
195553310192				2
195553309899	10	2	2	4		2
195553310093						1
195553971324
195553646147				2		2

Will this work?

[pavansai909 · Accepted solution]

Hi,

I have unpivoted the numerical columns and filtered the nulls. Does this help you?

[gullinnova]

this is of great help, unpivoting never occured to me thank you.