How to delete all Nulls cells and move text contained cell into left side.

[MohsinBipu]

Hello,

I have imported a PDF file into power query. It looks like the below.

Null position is very random. I cannot delete Row or Column based on filter... 

I want to delete all null cells and need to move the text-contained cells into the left side. My expected outcome will be as below-

[MohsinBipu]

@wdx223_Daniel As a Consequence of this query, I am posting a new topic to identify a text format (for my case XXX-XX-XX, example 122-22-10). and then take that cells including the upper cells. If you can help, please help to advise on the new topic.

Link: How to identify a text format in power query - Microsoft Power BI Community

[wdx223_Daniel · Accepted solution]

NewStep=Table.FormColumns(List.Zip(List.RemoveNulls(List.Transform(Table.ToRows(PreviousStepName),each if List.NonNullCount(_)=0 then null else List.RemoveNulls(_)))))

[MohsinBipu]

@wdx223_Daniel 

Tried to follow your formula. My bad luck is that this is not giving me results. Please help me how to apply?

[MohsinBipu]

@wdx223_Daniel Thanks. I sort it out. Thanks like magic. you are the real super user of PBI 🙂

= Table.FromColumns (List.Zip(List.RemoveNulls(List.Transform(Table.ToRows(#"Appended Query"),each if List.NonNullCount(_)=0 then null else List.RemoveNulls(_)))))

[adudani]

hi @MohsinBipu ,

reference :

The first link maps headers and checks if the column name contains "Column" after promotnig headers for nulls.

 (25) Dynamically Remove Empty Columns in Power Query - YouTube

This one uses unpivot to remove nulls. might not be the best solution for this

(25) DYNAMICALLY Remove All Empty Columns with some M MAGIC in Power BI - YouTube

let me know if this resolves with your use case. if not, could you please provide sample input file removing sensitive information?

[MohsinBipu]

@adudani Thanks for the below link. I watched and learn new things. But this is solving my problem as my NULL are very random. Thanks. 

[alannavarro]

Maybe there is another easy way to do this,
What I did is quite confusing:
1.- Select all columns and replace null for a special character that is not going to be in the data for example !
2.- Select all columns and merge all columns with the same special character as delimiter !
3.- Now you are going to have only 1 column. Select that column and replace values !! with nothing (an empty value)
4.- Select the column and split by delimiter !