Remove Empty Rows and columns

[sneha_bhiwagade1]

Hi Team,

I have below sample dataset. I have posted the sample data for 2 years. but in Actual I am having the dataset for 10 years. So I am looking for the suggestions to remove empty rows and columns. Data cleansing tool I know but it's not working in every conditions as I have the data in excel file. 

First step is to get the headers which i can achieve it using dynamic rename tool.

F1	F2	F3	F4	F5	F6	F7	F8	F9	F10	F11
Year		Q1 2019	Q2 2019	Q3 2019	Q4 2019		Q1 2020	Q2 2020	Q3 2020	Q4 2020
Roll no		0		0				0		0
Age			0	0
sub 1					0		1		2
sub 2		1						8
sub 3			2					3		0
sub 4				4					0

Could you please help me to achieve the same.

Many Thanks in advance :)

[Qiu · Accepted solution]

@sneha_bhiwagade1 
You are right, there is no Option to select "Dynamic or Unknown Fields" in Cleansing Tools.
so we can use the combination of Transpose and Cross Tab as below.

The cleansing tool used there is only to clean up the white space.

[Raj]

@sneha_bhiwagade1 
find workflow attached
mark done if solved.

[flying008 · Accepted solution]

Hi, @sneha_bhiwagade1 

FYI.