Transform Table

[anshpalash]

Hi all, I have data in format:

1	Scrap	12	null	Number of Products	10	null	null
2	null	Scrap	12	null	null	Number of Products	20
3	Scrap	9	null	Number of Products	13	null	null
4	null	Scrap	14	null	Number of Products	27	null
5	Scrap	null	12	null	null	Number of Products	45
6	Scrap	3	null	Number of Products	25

Location of 'Number of Products' is different for every file. I want to convert this into format: 

	Number of Products
1	10
2	20
3	13
4	27
5	45
6	25

Can somebody please help?

[v-janeyg-msft · Accepted solution]

Hi, @anshpalash 

You can merge other columns and create a custom column to display your desired result in Power Query.

Like this:

Text.Range([Merged],Text.PositionOf([Merged],"Number of Products,")+19,2)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Natallia_23]

Hi, Do you have column headers in file and if the numer of columns is known?
I did this in PowerQuery:

add custom column:
=if Text.Contains([Column1], "Number of Products") then [Column1]
else if Text.Contains([Column2], "Number of Products") then[Column2]
else if Text.Contains([Column3], "Number of Products") then[Column3] else 0

then split by delimiter

and remove all exccept id and last column.